Delete Duplicate Emails
Delete Duplicate Emails

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallestId.

For example, after running your query, the above Person table should have the following rows:


Solution:

Approach 1:

(If the above condition like : p1.id<p2.id AND p1.Email=p2.Email, will keep highest id and removes other duplicate emails)
Approach 2:

Update: Since people Googling for removing duplicates end up here
Although the OP’s question is about DELETE, please be advised that using INSERT and DISTINCT is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE, it took more than 2 hours and yet didn’t complete.