UNION ignores my embedded ORDER clause

[piecko]

Hi everybody,
I've got a problem with this SQL-Statement:

(SELECT * FROM currencies WHERE name = 'EUR') UNION ALL (SELECT * FROM currencies WHERE  name = 'USD')
UNION ALL (SELECT * FROM currencies WHERE name !='EUR' AND name != 'USD' ORDER BY name ASC)

The table "currencies " contains over 100 different currencies and I want get EUR first, then USD and at least the rest of the table in alphabetical order.

But the problem seems to be that the ORDER BY clause in the last SELECT is ignored. The Output is definitly not ordered in any way. I checked this by changing the order-direction to desc.
Am I just to stupid or is this a problem within MySQL ?

Greetz Piecko

[Atli]

That's odd. I would have thought that should work.

I came up with an alternative tho.
You can simply add a integer column to your query and give that column a value that you can then sort the results based on. That is; if the name of the currency is 'EUR', then you give it the value 1. If it is 'USD', then you give it the value 2. If it is anything else, you give it the value 3.
Then you can simply have your ORDER BY clause sort the result by the integer column first, and then by then name column.

For example:

SELECT 
    c.`Name`,
    (CASE c.`Name`
        WHEN 'EUR' THEN 1
        WHEN 'USD' THEN 2
        ELSE 3
    END) AS `DisplayOrder`
FROM `Currency` AS c
ORDER BY `DisplayOrder`, `Name` DESC;