change the mysql order of the result with union

Question

So i get 10 results from my first select and 1 from the other one after union like this:

 (SELECT    a.*,
            b.*
FROM        all a,
            names b
WHERE       b.name_id = a.name_id
ORDER   BY name_id DESC
LIMIT   10)
UNION
(SELECT a.*,
            b.*
FROM        all a,
            names b
WHERE       b.name_id = a.name_id
ORDER   BY request_id ASC
LIMIT   1)

i would like to get the result of the second select as the second last result like this

********
name_id 100
name_id 99
name_id 98
name_id 97
name_id 96
name_id 95
name_id 94
name_id 93
name_id 92
name_id 1 <- second select result as second last result
name_id 91
********

Can someone help pls?

Welcome to SO. Please see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Jun 26 '20 at 08:31
I just want to change the order of the result and set some result of the second query at the second last point. that it — ceppos, Jun 26 '20 at 08:36

P.Salmon · Answer 1 · 2020-06-26T09:27:39.070

Interesting question given

+----+--------+
| id | sname  |
+----+--------+
|  1 | sname1 |
|  2 | sname2 |
|  3 | sname3 |
|  4 | sname4 |
|  5 | sname5 |
|  6 | sname6 |
+----+--------+
6 rows in set (0.001 sec)

(select id,sname,@r:=@r+1 rn
from users 
cross join(select @r:=0) r
order by sname desc limit 3
) 
union 
(
select u.id,u.sname,
    @r:=@r - .9
    from users u
    left join (select id from users order by sname desc limit 3) u1 on u1.id = u.id
    where u1.id is null
    order by u.id asc limit 0,1 
)
order by rn;

Where a variable is used to calculate a row number in the first sub query, since this variable is not reset in the second query a simple piece of arithmetic works out where to position the second sub query result. Note the second sub query uses a left join to check that the result has not already appeared in the first sub query,

collapsar · Answer 2 · 2020-06-26T09:10:09.053

Synthesize a row number column for the query as it stands and shuffle positions as needed.

    SELECT x.name
         , x.name_id
      FROM (
            SELECT @rownum:=@rownum + 1 as row_number, 
                   t.name,
                   t.name_id
            FROM ( 
                -- original query from the question starts here
                (SELECT     b.name,
                            a.name_id
                FROM        allx a,
                            names b
                WHERE       b.name_id = a.name_id
                ORDER   BY name_id DESC
                LIMIT   10)
                UNION
                (SELECT     b.name,   
                            a.name_id 
                FROM        allx a,
                            names b
                WHERE       b.name_id = a.name_id
                ORDER   BY request_id ASC
                LIMIT   1)
            ) t,
            (SELECT @rownum := 0) r
           ) x
  ORDER BY CASE row_number
              WHEN 10 THEN 11
              WHEN 11 THEN 10
              ELSE row_number
           END
         ;

(Note that the query has been sightly modified to avoid syntax errors / support the demo: table all has been named allx, explicit projections of the union's subqueries).

That gets complicated quickly thus next to ad hoc reporting it is preferable to synthesize an attribute in the subqueries of the union that reflects a global order.

Demo here (SQL fiddle)

Credits

Row number synthesizing taken from this SO answer

score 0 · Answer 3 · answered Jun 26 '20 at 11:25

I would suggest union all and three selects:

SELECT an.*
FROM ((SELECT a.*, n.*, 1 as ord
       FROM all a JOIN
            names n
            ON  n.name_id = a.name_id
      ORDER BY n.name_id DESC
      LIMIT 9
     ) UNION ALL
     (SELECT a.*, n.*, 3 as ord
       FROM all a JOIN
            names n
            ON  n.name_id = a.name_id
      ORDER BY n.name_id DESC
      LIMIT 9 OFFSET 9
     ) UNION ALL
     (SELECT a.*, b.*
      FROM all a JOIN
           names n
      WHERE n.name_id = a.name_id
     ORDER BY request_id ASC
     LIMIT 1
    )
   ) an
ORDER BY ord, name_id;

change the mysql order of the result with union

3 Answers3