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 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,
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
where clause then says: Keep this row if
amnt1 is the same as
max1 is really the maximum. Similarly, keep the row if
amnt2 is the same as
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.