Sorting with an SQL UNION query

Question

I have two queries I would like to append in MySQL. I have found that UNION can help me do this, but it is not perfect as it does not retain the order of each appended query. In clear, I would like to do this:

(SELECT name,city FROM people WHERE xxx ORDER BY yyy) 
UNION 
(SELECT name,city FROM people WHERE zzz ORDER BY www)

but SQL won't execute on the ORDER statements when there is a UNION

One solution would be to add a bogus field to each subquery and order by that field first :

(SELECT name,city,'0' as bogus FROM people WHERE xxx) 
UNION 
(SELECT name,city,'1' as bogus FROM people WHERE zzz) 
ORDER by bogus ASC, wwwzzz

but this is deeply inefficient as the query has to go through all fields to sort on the bogus field. Do you know of any workaround?

Thank you

Actually, that is not "deeply inefficient". That sounds like exactly what you want. — TehShrike
– TehShrike, Commented Nov 9, 2011 at 9:47
Thank you. Well each select takes less than 1ms (including the ORDER statement). So I would expect a way to append the two in that same amount of time. But sorting on the bogus field brings the processing time to 500ms, way too much in my opinion. — David
– David, Commented Nov 9, 2011 at 15:31

Kevin Burton · Accepted Answer · 2011-11-09 16:36:52Z

2

Have you tried to use a UNION ALL rather than UNION ?

see: http://dev.mysql.com/doc/refman/5.0/en/union.html

e.g:

(SELECT name,city,'0' as bogus FROM people WHERE xxx) 
UNION ALL
(SELECT name,city,'1' as bogus FROM people WHERE zzz) 
ORDER by bogus ASC, wwwzzz

edited Nov 9, 2011 at 16:36

answered Nov 9, 2011 at 10:04

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

do you mean using: (SELECT name,city FROM people WHERE xxx ORDER BY yyy) UNION ALL (SELECT name,city FROM people WHERE zzz ORDER BY www) ? Doesn't seem to work (the order by statements are apparently ignored).

Kevin Burton Over a year ago

you are correct....'use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows'

Myles McDonnell · Accepted Answer · 2011-11-09 10:00:15Z

0

I'm completely winging it here as I only have experience with MsSql so please excuse me if this is useless, but can you store the unordered result set into a temporary table then select that with the required order clause? Or similarly make the union statement a sub/inner query and place the order clause on the outer query?

answered Nov 9, 2011 at 10:00

Myles McDonnell

13.4k20 gold badges70 silver badges123 bronze badges

Comments

Benoit · Accepted Answer · 2011-11-09 10:04:57Z

0

First, don't use UNION but UNION ALL. UNION will remove all duplicates and might therefore sort the data.

Also, the global syntax of a SQL query is of this kind:

sql_query: compound_statement [order_by_clause]
compound_statement: select_statement [ set_operator compound_statement ]

Therefore you can only use ONE Order By that is evaluated after UNION or INTERSECTION. Use subqueries if you want to sort partial results.

answered Nov 9, 2011 at 10:04

Benoit

79.7k24 gold badges226 silver badges241 bronze badges

Collectives™ on Stack Overflow

Sorting with an SQL UNION query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related