0

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

3
  • If you want the result ordered, you need to order by at the outermost level Commented Jun 25, 2020 at 15:53
  • Ordering in subqueries will be lost in outer query.
    – Akina
    Commented Jun 25, 2020 at 16:05
  • Which place in the resultset do you want *myint*? You are grabbing it twice, then de-dupping it.
    – Rick James
    Commented Jun 26, 2020 at 5:54

2 Answers 2

3

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

6
  • That can be further sped up by using UNION ALL instead of the default of UNION DISTINCT.
    – Rick James
    Commented Jun 26, 2020 at 5:49
  • The original question seemed to want "5" included in the output. So, change one of the inequalities.
    – Rick James
    Commented Jun 26, 2020 at 5:53
  • @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. Commented Jun 26, 2020 at 6:11
  • (MySQL's Optimizer is not that smart. Until very recently, all UNIONs involved an intermediate temp table. Now certain UNION ALLs avoid that overhead.)
    – Rick James
    Commented Jun 26, 2020 at 6:20
  • 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 Commented Jun 26, 2020 at 11:15
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.