In this Leetcode Delete Duplicate Emails problem solution we need to write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

Leetcode Delete Duplicate Emails problem solution


First Problem solution.

delete from Person
where Id in (select r.Id from  
             (select Id, row_number() over(partition by Email 
                                          order by Id
                                         ) as rk
             from Person) r
            where r.rk > 1
            );



Second Problem solution.

DELETE b
FROM Person a, Person b
WHERE a.Email = b.Email
AND a.Id < b.Id;


Third Problem solution.

delete from Person
where Id not in
(select mid from (select min(Id) as mid from Person
group by Email)T )