user1063108 - 7 months ago 38

SQL Question

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,

Can some one please help me out, I am not able to get this done

Thanks

Answer

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.