CODEWITHSUNDEEP
mysqlsql
TwystO
TwystO

Reputation: 2562

UNION ALL and ORDER BY at the same time

I have this query that does not work and I do not understand why.
Each SELECT statements should return descending results, but they're ordered ascendingly. Why ?

(SELECT * FROM table WHERE deleted_at = 0 ORDER BY id DESC)  
UNION ALL  
(SELECT * FROM table WHERE deleted_at <> 0 ORDER BY id DESC)  
LIMIT 0,30

I have to say, this query does not generates any error and the results are what I expect. They are just not well ordered.

Upvotes: 0

Views: 62

Answers (3)

FloChanz
FloChanz

Reputation: 3429

Because you apply the ORDER BY statement before the UNION ALL happens so on just one part of your data, you want to apply it on the whole result and it should be something like this : 

SELECT * FROM table WHERE deleted_at = 0  
UNION ALL  
SELECT * FROM table WHERE deleted_at <> 0 
ORDER BY id DESC
LIMIT 0,30

Upvotes: 0

Wrikken
Wrikken

Reputation: 70540

from the manual:

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

If you need it sorted in total:

SELECT * FROM table WHERE deleted_at = 0
UNION ALL  
SELECT * FROM table WHERE deleted_at <> 0
ORDER BY deleted_at = 0 DESC, id DESC
LIMIT 0,30

But this is of source the same as:

SELECT * FROM table 
ORDER BY deleted_at = 0 DESC, id DESC
LIMIT 0,30

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

There is no guarantee of ordering when using subqueries. If you want the results ordered by id descending, then use:

(SELECT * FROM table WHERE deleted_at = 0)  
UNION ALL  
(SELECT * FROM table WHERE deleted_at <> 0)  
order by id desc
LIMIT 0,30;

However, I think the query you really want is:

select *
from table
order by deleted_at = 0 desc, id desc
limit 0, 30;

This puts the deleted_at = 0 rows first and then fills out the data with the rest of the data.

Note: if deleted_at can be NULL and you want to filter them out too, then add a where clause for this filtering.

Upvotes: 1

Related Questions