user1063108 - 1 month ago 7x
SQL Question

SQl 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

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 highlited 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,
Thanks

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 `max2`is 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.