when using max with union all the select return first row value as result-CodePudding

SELECT max(t.a),t.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;

result = 40 1
result needed = 40 4

CodePudding user response：

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;

CodePudding user response：

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