CODEWITHSUNDEEP
mysqlsqlunion
David
David

Reputation: 1908

Sorting with an SQL UNION query

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

Upvotes: 0

Views: 2036

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

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

Upvotes: 2

Benoit
Benoit

Reputation: 79185

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.

Upvotes: 0

Myles McDonnell
Myles McDonnell

Reputation: 13335

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?

Upvotes: 0

Related Questions