UNION after ORDER BY and LIMIT

mysql sql sql-order-by union
lvil picture lvil · Jun 1, 2011 · Viewed 18.7k times · Source

My goal is to execute two different queries and then combine them.
My code is: 

SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0,1 
UNION   
SELECT * FROM some tables WHERE ...

I get the following error:

#1221 - Incorrect usage of UNION and ORDER BY

It is important that ORDER BY is only for the first query. How can I perform this task?

Answer

Salman A picture Salman A · Jun 1, 2011

You can use parenthesis to allow the use of ORDER/LIMIT on individual queries:

(SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0, 1)
UNION   
(SELECT * FROM some tables WHERE ...)
ORDER BY 1   /* optional -- applies to the UNIONed result */
LIMIT 0, 100 /* optional -- applies to the UNIONed result */

Related questions

Using union and order by clause in mysql

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data …

Read More
How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY `…

Read More