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.
CodePudding user response：
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;