SELECT UNION and ORDER BY in mysql.. how to ?

Question

Hi folks, i would like to get from a single table, all rows, but order them in different ways. For example i write

(SELECT * FROM table1
ORDER BY fieldA ASC LIMIT 3
)
UNION
(
SELECT * FROM table1
ORDER BY FieldB DESC
)

It works, excpet that the second order by (FIELDB DESC) is ignored... Somebody know Why ? Thank you

Answer 1

The UNION operator performs an implied sort as part of the union operation (IIRC, on the key column(s)).

If you want other sorting in the result, you have to apply an ORDER BY to the unioned selection.

In your case, you need some way to distinguish between the first selection and the second, so that you can order the union properly. Something like (untested):

(SELECT table1.*, 0 AS TMP_ORDER FROM table1 ORDER BY fieldA ASC LIMIT 3)
UNION
(SELECT table1.*, 1 AS TMP_ORDER FROM table1)
ORDER BY TMP_ORDER ASC, 
CASE WHEN TMP_ORDER = 0 THEN fieldA ELSE 0 END ASC, 
CASE WHEN TMP_ORDER = 1 THEN fieldB ELSE 0 END DESC

The problem with this approach is that you'll have duplicates for the three rows selected as part of the first query in the UNION (since the columns don't totally match).

Are you sure you can't use two SELECT statments instead?