SQL: SELECT with UNION, ORDER BY and LIMIT

问题:

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 ;-)

回答1:

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.

回答2:

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
    )

回答3:

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

回答4:

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.

回答5:

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.