Search code examples
sqlsqlitesql-order-byunionlimit

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

Solution

  • 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.