CODEWITHSUNDEEP
sqlsqlite
nabulke
nabulke

Reputation: 11275

Combine two statements with LIMITS using UNION

Is there a way to combine these two statements into one without having duplicate entries?

SELECT * FROM Seq where JULIANDAY('2012-05-25 19:02:00')<=JULIANDAY(TimeP) 
         order by TimeP limit 50

SELECT * FROM Seq where JULIANDAY('2012-05-29 06:20:50')<=JULIANDAY(TimeI)  
         order by TimeI limit 50

My first, obvious attempt is not supported by SQLITE (Syntax error: Limit clause should come after UNION not before):

SELECT * FROM Seq where JULIANDAY('2012-05-25 19:02:00')<=JULIANDAY(TimeP) 
         order by TimeP limit 50
UNION
SELECT * FROM Seq where JULIANDAY('2012-05-29 06:20:50')<=JULIANDAY(TimeI)
         order by TimeI limit 50

Upvotes: 24

Views: 13240

Answers (4)

everestial
everestial

Reputation: 7255

I have a table buysell_product. I want to select top 5 based on views_1 column and another top 5 using views_2 column, then merge the two. Columns are the same in both queries. Expecting this to work, it does not:

SELECT id, name, views_1, views_2
    FROM buysell_product
    ORDER BY views_1 DESC
    LIMIT 5
UNION
SELECT id, name, views, views_2
    FROM buysell_product as b
    ORDER BY views_2 DESC
    LIMIT 5

Error:

Execution finished with errors.
Result: ORDER BY clause should come after UNION not before
At line 1:
...

They work separately but I need to merge them:

SELECT * FROM (
    SELECT id, views_1, views_2, name
    FROM buysell_product
    ORDER BY views_1 DESC
    LIMIT 5
)
UNION
SELECT * FROM (
    SELECT id, views_1, views_2, name
    FROM buysell_product as b
    ORDER BY views_2 DESC
    LIMIT 5
)

Result:

id views_1 views_2 name
2 41 16 Excellent 2013 ford ecosport
3 72 10 Excellent Hyundai creta
5 39 39 iPhone 11 128gb
7 12 84 Excellent Hyundai creta sx
9 37 84 Volkswagen Polo 1.2 GT AMT 2017
44 34 81 Usupso Massage Anti Skid Slippers
45 15 75 Garlic Powder - 100Gm
57 35 11 Iphone 13 and 14
67 15 73 Universal Touch Screen Capacitive Stylus

Upvotes: 0

ejb_guy
ejb_guy

Reputation: 1125

SELECT * from
    (SELECT *
    FROM Seq
    where JULIANDAY('2012-05-25 19:02:00')<=JULIANDAY(TimeP)
    order by TimeP limit 50)
UNION
SELECT * from
    (SELECT *
    FROM Seq
    where JULIANDAY('2012-05-29 06:20:50')<=JULIANDAY(TimeI)
    order by TimeI limit 50)

Upvotes: 2

vyegorov
vyegorov

Reputation: 22905

Queries are processed in stages:

  1. FROM clause and all the joins;
  2. WHERE clause and all the predicates. So if you whant to see NULL values in the result set, you should never filter OUTER-joined table columns in the WHERE section, as this will turn your query into INNER join;
  3. GROUP BY and HAVING clause;
  4. Query combinations: UNION, INTERSECT, EXCEPT or MINUS
  5. ORDER BY
  6. LIMIT

Therefore, as others pointed out, it is syntatically wrong to use ORDER BY and LIMIT before UNION clause. You should use subqueries:

SELECT *
  FROM (SELECT * FROM Seq
         WHERE JULIANDAY('2012-05-25 19:02:00') <= JULIANDAY(TimeP) 
         ORDER BY TimeP LIMIT 50) AS tab1
UNION
SELECT *
  FROM (SELECT * FROM Seq
         WHERE JULIANDAY('2012-05-29 06:20:50') <= JULIANDAY(TimeI)  
         ORDER BY TimeI LIMIT 50) AS tab2;

Upvotes: 10

GarethD
GarethD

Reputation: 69789

Use subqueries and perform the limit within them.

SELECT  *
FROM    (   SELECT  * 
            FROM    Seq 
            WHERE   JULIANDAY('2012-05-25 19:02:00') <= JULIANDAY(TimeP) 
            ORDER BY TimeP
            LIMIT 50
        )
UNION
SELECT  *
FROM    (   SELECT  * 
            FROM    Seq 
            WHERE   JULIANDAY('2012-05-29 06:20:50') <= JULIANDAY(TimeI) 
            ORDER BY TimeI
            LIMIT 50
        )

Upvotes: 45

Related Questions