MySQL UNION with Multiple ORDER BY

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

• Lennart - Slava Ukraini: If you want the result ordered, you need to order by at the outermost level
• Akina: Ordering in subqueries will be lost in outer query.
• Rick James: Which place in the resultset do you want *myint*? You are grabbing it twice, then de-dupping it.

Answer 1

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

• Rick James: That can be further sped up by using UNION ALL instead of the default of UNION DISTINCT.
• Rick James: The original question seemed to want "5" included in the output. So, change one of the inequalities.
• Lennart - Slava Ukraini: @RickJames, regarding UNION ALL, In principle yes, the legs are guaranteed disjoint via origin but each leg may contain duplicates (since SQL is based on bags, not sets). Anyhow, it is just a sketch so I'll leave it as is.
• Rick James: (MySQL's Optimizer is not that smart. Until very recently, all UNIONs involved an intermediate temp table. Now certain UNION ALLs avoid that overhead.)
• Lennart - Slava Ukraini: Yes, but my point was that union all may change the result. One of the absurd consequenses of a bag based algebra is that |A|>=|A U A|, so UNION ALL could potentially change the result

Answer 2

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