SQL Remove Maximum per partition

user_19050542 0
Viewed 22

I would like not to take into consideration one row per id: PONUMBER that has multiple ORD Table

PONUMBER ORD QTYORDER
1 1 200
1 2 100
1 3 100

As you may notice the QTYORDER is the sum of other lines, so I want not to take into consideration and remove it. In this example, we have the first line has the QTYORDER which the sum of other lines.

I was thinking about if QTY is the sum of other ORD per PONUMBER it should be remove.

gp sql
1 Answers

You can use row_number like this:

with u as
(select *, 
row_number() over(partition by PONUMBER order by QTYORDER desc) as rn
from table_name)
select PONUMBER, ORD, QTYORDER from u
where rn <> 1;

Fiddle

user_176450990
Related