Re: ORDER BY ignored in UNION

[Paul Whipp]

In case anyone else finds this topic long after the event :-
Internal ORDER BY clauses are ignored.

Not recommended for large results but you can add a field to the union specifically for ordering to avoid the need for unnecessary decoration on your desired values. Here is a real example where a client wanted a few 'special' options at the top of a list with just the remainder of the list in alphabetical name order:

(SELECT
CONCAT(id,",0") as "value",
name as "label",
CONCAT("AAAAAA",name) as ordering
FROM
pp_locations
WHERE
pp_locations.id NOT IN (2, 3, 4))
UNION
(SELECT CONCAT("4,",pp_customers.home_address_id) as "value",
CONCAT("Home of ",pp_customers.firstname," ",pp_customers.lastname) as "label",
"AAAAAB" as ordering
FROM
pp_customers
JOIN pp_orders ON pp_customers.user_id = pp_orders.customer_id
WHERE
pp_orders.id = 2)
UNION
(SELECT
CONCAT("2,",id) as "value",
name as "label",
CONCAT("AAAAAC",name) as ordering
FROM
pp_special_locations)
UNION
(SELECT
CONCAT("3,",id) as "value",
name as "label",
CONCAT("AAAAAD",name) as ordering
FROM
pp_vets
WHERE
is_agency = 1)
ORDER BY ordering;

Pretty nasty but it got the job done.