I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns.


id   name   age   x
1   peter   25    II
2   peter   25    II

The table has tens of thousands of rows.

  • 2
    please add more sample data and desired results
    – Tom V
    Commented May 16, 2015 at 17:32
  • Which record should be kept? The one with the lowest ID? Are there any dependent records? Commented May 16, 2015 at 19:03

2 Answers 2


Supposing you have no foreign keys referencing that table, you could do something like

create table some_table as
select min(id), name, age, x
from t
group by name, age, x

then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.

  • 2
    I like to keep the old data around for a short period when doing somewhat dangerous operations such as this. Instead of dropping the old table, I'd rename it. Sometimes I drop the table soon after I've finished. Other times I'll drop it a few days or weeks later. Commented May 16, 2015 at 19:05

Even if the question is 6 years old

I had a similar need and I used something like that

    FROM t 
    NOT IN
    FROM t 
    GROUP BY id, name, age)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.