SELECT UNION and ORDER BY in mysql.. how to?
i would like to get from a single table, all rows, but order them in different way开发者_StackOverflow社区s. 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
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?
You can declare the second SELECT
as a fixed result..
SELECT 'First select option' AS something
UNION
SELECT something
FROM(
(SELECT something
FROM SomeTable
ORDER BY something ASC)) FixedResult
精彩评论