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
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
With subselect and ordr by is a little tricky
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 |
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