By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
_order varchar(20),
item varchar(20)
);
insert into mytable values
('1234', '001'),
('1234', '002'),
('1235', '001'),
('1235', '002'),
('1235', '003'),
('1236', '001'),
('1237', '001'),
('1234', '001'),
('1234', '002'),
('1235', '001'),
('1235', '002'),
('1235', '003'),
('1236', '001'),
('1237', '001');
14 rows affected
with cte as (
SELECT _order, item,
Row_Number() OVER(PARTITION BY _order, item ORDER BY _order) as CN
FROM mytable
)
select *
from cte
WHERE CN > 1
| _order | item | CN |
|---|---|---|
| 1234 | 001 | 2 |
| 1234 | 002 | 2 |
| 1235 | 001 | 2 |
| 1235 | 002 | 2 |
| 1235 | 003 | 2 |
| 1236 | 001 | 2 |
| 1237 | 001 | 2 |
with cte as (
SELECT _order, item,
Row_Number() OVER(PARTITION BY _order, item ORDER BY _order) as CN
FROM mytable
)
Delete from cte WHERE cn > 1;
select * from mytable;
| _order | item |
|---|---|
| 1234 | 001 |
| 1235 | 001 |
| 1235 | 003 |
| 1237 | 001 |
| 1234 | 002 |
| 1235 | 002 |
| 1236 | 001 |