Question 21514 in Mysql Question posted in
The official MySQL documentation can be found here.

when using max with union all the select return first row value as result – Mysql

SELECT max(t.a),t.b 
from(
 SELECT 10 as a,1 as b,1 as c 
 UNION ALL
 SELECT 20,2,1 
 UNION ALL
 SELECT 30,3,2  
 UNION ALL
 SELECT 40,4,2 ) as t 
 order by b desc;

result = 40,1,1

result needed = 40,4,2
20,2,1
result to return group by column c get the max of a and the b column with it .

Tags:

2

Answers


  1. 0

    Seems you need the first row when ordered:

    select a, b
from (
 select 10 as a,1 as b 
 union all
 select 20,2 
 union all
 select 30,3  
 union all
 select 40,4 
) as t 
order by b desc
limit 1;
    Login or Signup to reply.
  2. 0

    The query in the questions seems incorrect, it will fail with sql_mode=only_full_group_by enabled, which I strongly recommended to be enabled.

    If you want to handle ties , you could use:

    select a,b
from  (  select a,
                b,
                row_number() over (order by a desc ) as rn
         from ( SELECT 10 as a,1 as b 
                UNION ALL
                SELECT 20,2 
                UNION ALL
                SELECT 30,3  
                UNION ALL
                SELECT 40,4 
              ) x
    ) xx
where rn=1;

    https://dbfiddle.uk/bvCKF0od

    On MySQL < 8.0, to handle ties you could use:

    select x.a,
       x.b
from ( SELECT 10 as a,1 as b 
                UNION ALL
                SELECT 20,2 
                UNION ALL
                SELECT 30,3  
                UNION ALL
                SELECT 40,4 
     ) x
inner join (select max(a) as a
            from ( SELECT 10 as a,1 as b 
                   UNION ALL
                   SELECT 20,2 
                   UNION ALL
                   SELECT 30,3  
                   UNION ALL
                   SELECT 40,4 
                  ) xx
           )as tbl on tbl.a=x.a;

    https://dbfiddle.uk/_e97v_zi

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search