Search code examples
sqlpostgresqlduplicatessql-delete

SQL Delete duplicate records based on two columns

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)

Input table

So the output table should be:

desired output

Thanks!!

Solution

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