SQL: SELECT with UNION, ORDER BY and LIMIT

Question

I'm getting Errors that ORDER by should come after UNION but i want these to queries ordered before combined to one and then limited to 10.

SELECT * 
  FROM (SELECT time, x, y, z 
          FROM db 
         WHERE time >= now 
      ORDER by time, x
       UNION 
       SELECT time, x, y, z 
         FROM db 
        WHERE time < now 
     ORDER by time, x) 
LIMIT 10

I hope you understand, what I'm trying to do and can help me ;-)

It's for SQLITE: android.database.sqlite.SQLiteException: ORDER BY clause should come after UNION not before: — Tobias, Jan 14 '11 at 23:24

score 35 · Answer 1 · answered Feb 01 '12 at 05:25

35

if you have a very complex query in SQLite but need to use UNION with ordering, then you can try

select * from (
    select * from b ORDER BY date asc
    )
UNION
select * from (
    select * from b ORDER BY name desc
    )
UNION
select * from (
    select * from b ORDER BY gender asc
    )

answered Feb 01 '12 at 05:25

hmaxf2

656
5
10

This is exactly what I was looking for! I was trying to figure out what I needed besides the parentheses. – Greg G Feb 08 '22 at 21:47
At least in SQL Server the outer selects need to be named i.e. `SELECT * FROM (...) AS x`. – Jonas Äppelgran Jan 31 '23 at 09:25

score 7 · Answer 2 · edited Jun 09 '11 at 23:25

7

An order by will affect the ENTIRE union.

Anyway, it looks like you want the rows nearest to now. You could try this:

SELECT   time, x, y, z 
FROM     db 
ORDER BY ABS(time - now) ASC
LIMIT    10

edited Jun 09 '11 at 23:25

Matt

1,284
14
22

answered Jan 14 '11 at 23:20

Andomar

232,371
49
380
404

score 5 · Answer 3 · answered Jan 14 '11 at 23:28

In "standard" (for some definition of "standard") SQL;

select top 10 *
from (       select time,x,y,z from db where time > now
       union select time,x,y,z from db where time < now
     ) t
order by t.time

How to limit the number of rows in the result set may vary between SQL implementations.

score 3 · Accepted Answer · edited May 23 '17 at 12:34

3

That's not how it works, at least in MySQL (you didn't specify). The ORDER operation comes after the data is selected and all UNIONs, GROUP BYs, etc. have been performed.

See SQL Server: ORDER BY in subquery with UNION for a way around this.

edited May 23 '17 at 12:34

Community

1
1

answered Jan 14 '11 at 23:20

coreyward

77,547
20
137
166

score 2 · Answer 5 · answered Aug 05 '15 at 14:04

Any easy solution I applied was as follows :

SELECT 
 name,
 TO_DATE(date1, 'DD-MON-YYYY HH:MI AM')
FROM TableX

UNION

SELECT
 name,
 TO_DATE(date2, 'DD-MON-YYYY HH:MI AM')
FROM TableY

ORDER BY 2 DESC;

This orders the results by the second (date) column.

If the date column is a string, you can apply the TO_DATE function as shown above, else it is not necessary.

SQL: SELECT with UNION, ORDER BY and LIMIT

5 Answers5

Linked