CODEWITHSUNDEEP
mysqlsqlsql-order-byunion
lvil
lvil

Reputation: 4326

UNION after ORDER BY and LIMIT

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?

Upvotes: 26

Views: 22927

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272166

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 */

Upvotes: 40

Sona Rijesh
Sona Rijesh

Reputation: 1035

(SELECT user_id AS id FROM tbl_user)
UNION
(SELECT address_id AS id FROM tbl_address)
ORDER BY id ASC LIMIT 10

Upvotes: 2

Bohemian
Bohemian

Reputation: 425083

SELECT * FROM (SELECT * FROM some tables WHERE ... ORDER BY field1 LIMIT 0,1) x
UNION ALL
SELECT * FROM some tables WHERE ...

Note the use of UNION ALL:

  • UNION removes duplicate rows from the result set and the DB orders all the rows before doing this (so the entire result set is sorted)
  • UNION ALL preserves both order and duplicates

Upvotes: 11

luca
luca

Reputation: 37136

just put everything in round brackets:

(SELECT * FROM table1 ORDER BY datetime  )
UNION   
(SELECT * FROM table2 ORDER BY datetime DESC)

Upvotes: 4

Related Questions