user1063108 user1063108 - 11 months ago 44
SQL Question

SQL Server group by and max value for multiple column

I am trying to get a row where one of the two columns have a max value for each group, for e.g in the image below

Table Data

as you can see each unit has multiple rows with boxid, amnt1 and amnt2.

What I am after is once the query runs, it returns the lines highlighted meaning it will return the unit id, with boxid of the row with MAX(amnt1),MAX(amnt2) among the groups rows. So in the case of unit 10002 largest amnt1 is 1.60 therefore output is

10002, 156, 1.60,

Can someone please help me out? I am not able to get this done


Answer Source

Here is a method that calculates the maximum for each column and then uses that information to find the overall row:

select t.*
from (select t.*,
             max(amnt1) over (partition by unit) as max1,
             max(amnt2) over (partition by unit) as max2
      from t
     ) t
where (t.amnt1 = max1 and max1 >= max2) or
      (t.amnt2 = max2 and max2 >= max1);

The way this works is by computing the maximum for each of the two columns for each unit. These maxima are in max1 and max2.

The where clause then says: Keep this row if amnt1 is the same as max1 and max1 is really the maximum. Similarly, keep the row if amnt2 is the same as max2 and max2is really the maximum.

The solution given by @ZLK is more general. If you had more columns then the logic gets more complicated to explain this way (eh, not really that hard, but apply would then look simpler). But for two columns, the apply nested before the row_number() starts to look a bit complicated.