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
SELECT * FROM table1
ORDER BY FieldB DESC
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?