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

Solution 1:

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
    )

Solution 2:

An order by will affect the ENTIRE union.

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

SELECTtime, x, y, z 
FROM     db 
ORDERBYABS(time- now) ASC
LIMIT    10

Solution 3:

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

select top 10*from (       selecttime,x,y,z from db wheretime> now
       unionselecttime,x,y,z from db wheretime< now
     ) t
orderby t.time

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

Solution 4:

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.

Solution 5:

Any easy solution I applied was as follows :

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

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

ORDERBY2DESC;

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.