MySQL UNION with Multiple ORDER BY

Posted on

Question :

I have tried to make this work in multiple forms but none have been successful. I need to read an int from my table to order my data as desired. I have previously gotten this to work as multiple queries but Im hoping to do it all in one now.

    SELECT * FROM (
        (SELECT * FROM table_a WHERE int_a <= *myint* ORDER BY int_a DESC)
        UNION 
        (SELECT * FROM table_a WHERE int_a >= *myint* ORDER BY int_a DESC)
    ) uniontable

MySQL 5.7

Answer :

I’m not exactly sure what you re trying to achieve, but if you want the rows from the first leg to be ordered first, then you can add an attribute that determines from which leg the row belongs:

select myint from (
    select 1 as origin, myint from table_a where myint < 5
    union
    select 2 as origin, myint from table_a where myint > 5
) as t
order by origin, myint desc;

myint
4
3
2
1
9
8
7
6

Fiddle

With subselect and ordr by is a little tricky

so do

create table table_a (myint Integer)
INSERT INTO table_a VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)
SELECT myint
FROM
((SELECT myint, CONCAT('a',@rn := @rn +1) orderby FROM table_a,(SELECT @rn := 0)a WHERE myint < 5 
ORDER BY myint DESC LIMIT 18446744073709551615 )
UNION
(SELECT myint, CONCAT('b',@rn1 := @rn1 +1) orderby FROM table_a,(SELECT @rn1 := 0)a 
WHERE myint > 5 ORDER BY myint DESC LIMIT 18446744073709551615 )) mynewtable
ORDER BY orderby ASC
| myint |
| ----: |
|     4 |
|     3 |
|     2 |
|     1 |
|     9 |
|     8 |
|     7 |
|     6 |

db<>fiddle here

The concept is simple create two subwuery that have a clumn which can be ordery and keep the right here it is orderby

TZhe limit is needed, so mysql knpws that it should keep his order.

this works in mysql and maeiadb

Leave a Reply

Your email address will not be published. Required fields are marked *