UNION after ORDER BY and LIMIT

Question

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?

Read this [tutorial](http://www.mysqltutorial.org/sql-union-mysql.aspx) — luca, Jun 01 '11 at 08:03

score 36 · Accepted Answer · edited Dec 24 '16 at 22:57

36

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

edited Dec 24 '16 at 22:57

Jesse

8,605
7
47
57

answered Jun 01 '11 at 08:09

Salman A

262,204
82
430
521

2

syntax error... you need to select from the aliased (bracketed) select – Bohemian Jun 01 '11 at 08:13
hmmm - I checked too and got the error. Maybe I need to upgrade my mysql. Sorry! – Bohemian Jun 02 '11 at 01:44
it didnt work for me http://www.codeproject.com/Questions/1004382/maintain-order-of-two-queries-after-union – Mohammad Faizan khan Jun 26 '15 at 11:31

Bohemian · Answer 2 · 2011-06-01T08:12:46.463

11

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

edited Jun 01 '11 at 08:12

answered Jun 01 '11 at 08:07

Bohemian

412,405
93
575
722

score 4 · Answer 3 · answered Jun 01 '11 at 08:11

4

just put everything in round brackets:

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

answered Jun 01 '11 at 08:11

luca

36,606
27
86
125

1

syntax error... you need to select from the aliased (bracketed) select – Bohemian Jun 01 '11 at 08:15
I checked and got the error. Maybe I need to upgrade my mysql. Sorry! – Bohemian Jun 02 '11 at 01:44

score 2 · Answer 4 · answered Nov 22 '12 at 04:00

2

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

answered Nov 22 '12 at 04:00

Sona Rijesh

1,041
7
32
62

UNION after ORDER BY and LIMIT

4 Answers4

Related