How can I delete rows that are duplicated on some columns?

Question

I have the following table:

Id    color    size
1     blue     medium
2     blue     medium
3     blue     small
4     red      big

I to exclude all rows that have the color and size duplicated, but different Id. But bear in mind that I don't want to keep one of them, I want to exclude both that have color and size similar, but Id different. So this would be my final table:

Id    color    size
3     blue     small
4     red      big

I found this post Delete Duplicate Records in PostgreSQL and this one https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/. But both of them want to keep one of the rows. I don't want it.

Is there any way?

Are you sure that you want `2, blue, medium` in the results? — forpas, Jun 19 '20 at 18:35
Where does `(3, red, medium)` come from? It's not in the original data — , Jun 19 '20 at 18:36
@a_horse_with_no_name you are absolutely right, so sorry. Just fixed — Dumb ML, Jun 19 '20 at 19:15

forpas · Accepted Answer · 2020-06-19T18:50:16.173

With NOT EXISTS:

select t.* from tablename t
where not exists (
  select 1 from tablename 
  where id <> t.id and color = t.color and size = t.size
);

or with COUNT() window function:

select t.Id, t.color, t.size
from (
  select *, count(*) over (partition by color, size) counter
  from tablename
) t  
where t.counter = 1

If you want to delete the duplicate rows from the table then:

delete from tablename t
where exists (
  select 1 from tablename 
  where id <> t.id and color = t.color and size = t.size
);

See the demo.
Results:

| id  | color | size  |
| --- | ----- | ----- |
| 3   | blue  | small |
| 4   | red   | big   |

How can I delete rows that are duplicated on some columns?

1 Answers1