SQL Delete duplicate records based on two columns

Question

In postgresql

I need a lookup table to have unique values in two column. I have this list of cars but Golf is duplicated on Car and Shop columns. I want to remove all records where car and shop are the same (because horsepower and KM will be the same)

So the output table should be:

Thanks!!

please tag with database platform. Sounds like you also need to add the proper integrity constraints such as a primary key or uniqueness index to prevent future duplicates — OldProgrammer, Sep 16 '21 at 16:56
It is really just for a temporary lookup table, but once duplicates are removed primary key would be car and shop — Johnny, Sep 16 '21 at 17:03
You can identify duplicates with a query, but to remove all but one you'll probably need to run a procedure with a cursor. — Don R, Sep 16 '21 at 17:09
Does this answer your question? [Delete duplicate rows from small table](https://stackoverflow.com/questions/6583916/delete-duplicate-rows-from-small-table) — OldProgrammer, Sep 16 '21 at 17:24
@Johnny duplicate value based on car, shop, horsepower and KM or (car and shop) — Rahul Biswas, Sep 16 '21 at 17:34
@Johnny And in your given sample car = leon and shop = Madrid..but your output column shop will madrid and barcelona for loan. I think it's wrong. — Rahul Biswas, Sep 16 '21 at 17:40

score 0 · Accepted Answer · answered Sep 16 '21 at 18:25

You can use distinct on:

select distinct on (car, shop) t.*
from t
order by car, shop, day;

If you want to actually delete the records:

delete from t
   where t.day = (select min(t2.day)
                  from t2
                  where t2.car = t.car and t2.shop = t.shop
                 );

SQL Delete duplicate records based on two columns

1 Answers1