UNION ALL results are not sorted properly

Question

It is necessary to make a complex selection from the table with sorting for different conditions. When I make two requests separately, then everything works, and if I combine, it outputs without sorting - by the growth of the key field of the table

(SELECT * 
 FROM `user` 
 WHERE `user`.`rank_golos` >=22.1 
 order by `user`.rank_yearnub DESC, `user`.rank_golos DESC) 
UNION ALL 
(SELECT * 
 FROM `user` 
 WHERE `user`.`rank_golos` <22.1 and `user`.`rank_golos` >0 
 order by `user`.rank_golos DESC, `user`.rank_yearnub DESC)

Can you show the result you're expecting? – Barmar Nov 18 '17 at 16:52 — Barmar, Nov 18 '17 at 16:52
Those. give an example of a table? – Dr.Noname Nov 18 '17 at 17:28 — Dr.Noname, Nov 18 '17 at 17:28
Thanks, I've already solved the problem – Dr.Noname Nov 18 '17 at 18:01 — Dr.Noname, Nov 18 '17 at 18:01

Bohemian · Accepted Answer · 2017-11-18T17:31:13.480

0

union all outputs the results of each query serially - your rows would be ordered within each query result but not overall.

To effect an overall ordering, move the order by outside the two queries by creating an outer query to wrap the union all result.

To effect ordering that's different depending on which half of the union all the result came from, add information to the rows about the source of the row and use that to get the specific ordering:

SELECT * FROM (
    SELECT *, 1 as source 
    FROM `user` 
    WHERE `user`.rank_golos >= 22.1 
    UNION ALL 
    SELECT *, 2
    FROM `user` 
    WHERE `user`.rank_golos < 22.1 and `user`.rank_golos > 0
) x
order by
  case when source = 1 then rank_yearnub else rank_golos end DESC,
  case when source = 1 then rank_golos else rank_yearnub end DESC

If you need to exclude source from the query output, you could wrap the above and another outer query that just selects all columns other than source.

I've also removed the unnecessary brackets.

edited Nov 18 '17 at 17:31

answered Nov 18 '17 at 16:43

Bohemian

412,405
93
575
722

But he wants to order the two groups differently. Can you show how to do that outside the subqueries? – Barmar Nov 18 '17 at 16:44
Your ordering is not correct. The first group is supposed to be `ORDER BY rank_yearnub DESC, rank_golos DESC`. – Barmar Nov 18 '17 at 16:46
@Barmar skimmed it. Got it now though :) – Bohemian Nov 18 '17 at 16:48
But i have error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM `user` WHERE `user`.rank_golos >= 22.1 UNION ALL ' at line 2 – Dr.Noname Nov 18 '17 at 17:26
#1248 - Every derived table must have its own alias – Dr.Noname Nov 18 '17 at 17:30
Working. Thank you very much for your help! – Dr.Noname Nov 18 '17 at 17:35

score 0 · Answer 2 · answered Nov 18 '17 at 17:29

0

I found a solution

select * from user
where rank_golos >0
order by rank_golos >=22.1 desc, 
  if (rank_golos >=22.1, rank_yearnub, rank_golos) desc,
  if (rank_golos >=22.1, rank_golos, rank_yearnub) desc

answered Nov 18 '17 at 17:29

Dr.Noname

73
10

UNION ALL results are not sorted properly

2 Answers2