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!!

Answer 1

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
                 );