I am looking for a way to filter not only the duplicate rows, but also the "initial" row. The goal is to have a clean list of all positions. The list is used by sales / accounting to see open positions, thats why the initial "Invoice" position has to be removed as well if a "Cancellcation" exists for that invoice.
I've tried solutions with group by, subqueries and EXISTS, but can't get the expected result. Ideally, I get this to work as an additional filter inside the where clause.
Default
| ID | Nr | Type | Amount |
|---|---|---|---|
| 1 | NR-100 | Invoice | 100 |
| 2 | NR-101 | Invoice | 200 |
| 3 | NR-102 | Invoice | 300 |
| 4 | NR-100 | Cancellation | 100 |
| 5 | NR-102 | Cancellation | 300 |
| 6 | NR-103 | Invoice | 150 |
Expected results
| ID | Nr | Type | Amount |
|---|---|---|---|
| 2 | NR-101 | Invoice | 200 |
| 6 | NR-103 | Invoice | 150 |
CodePudding user response：
EXISTence test would seem to be the way to go so I wonder what problem you had with it..
select *
from t
where type = 'invoice' and
not exists (select 1 from t t1 where t1.nr = t.nr and t1.type = 'cancellation')