UNION ALL no respeta orden de ORDER BY???

[jurena]

En el manual de MySQL, dentro de las aportaciones de usuarios, se ofrecen otras dos, que creo que harán lo mismo que la que te propuse, aunque se utilizan subconsultas:

Posted by Phil McCarley on February 28 2006 6:37am [Delete] [Edit]

In addition to the above comment regarding the ORDERing of individual SELECTS, I was after a way to do exactly what is says wouldn't work. I have two playlists, and to get the correct order I need to use two different ORDER clauses, also I wanted to use the DISTINCT functionality of the UNION syntax.

What I needed was the contents of each playlist to be ordered in there specific way, while the first appeared wholly before the second. Also, I couldn't use the various tricks of adding extra colums to sort on because that left me with non-unique rows and therefore if the same entry was in both lists, the duplicate didn't get removed.

How I overcame this was to use subqueries, as follows:

SELECT song_id FROM
(SELECT song_id FROM play_immediate
ORDER BY play_id DESC) AS t1

UNION

SELECT song_id FROM
(SELECT song_id FROM play_later
ORDER BY play_id) AS t2

And using this I am able to sort each list differently, one ascending and one descending, keep the 'immediate' list before the 'later' list but still remove all duplicates.

Hope this helps others.



Posted by James McGuigan on June 14 2006 9:39am [Delete] [Edit]

If you want to run a WHERE statement on the result of a UNION, you can make the union into a subquery like such:

SELECT * FROM ((
SELECT * FROM table1
WHERE ...
ORDER BY ...
LIMIT ...
) UNION (
SELECT * FROM table2
WHERE ...
ORDER BY ...
LIMIT ...
)) as t
WHERE ...
ORDER BY ...