[Leetcode] Delete Duplicate Emails Remove duplicate mailboxes

Write a SQL query to delete all duplicate e-mail entries in a table named Person , keeping unique emails based on its smallest Id.

+----+------------------+| Id | Email            |+----+------------------+| 1  | [Email protected] | | 2  | [email protected]  | | 3  | [Email protected] |+----+------------------+id is the primary key, column for this table.

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

+----+------------------+| Id | Email            |+----+------------------+| 1  | [Email protected] | | 2  | [email protected]  | +----+------------------+

This problem let us delete duplicate mailbox, that we can first find all the non-duplicate mailbox, and then take a counter is duplicate mailbox, all deleted can, then we find out all the non-duplicate mailbox, we can follow the mailbox group up, and then use the MIN keyword to pick out the smaller, and then take the supplement set Delete:

Solution One:

DELETE  from WHERE  not inch (Select from,selectMINfromGROUP by (Email) p);

We can also use the intra-delivery to let two of the tables are linked to the mailbox, and then the same mailbox and the ID of the large delete, see the code as follows:

Solution Two:

DELETE  from JOIN  on=WHERE> p1. Id;

We can also use the where to associate two tables without a join:

Solution Three:

DELETE  from Person p1, person p2 WHERE =  and > p1. Id;

Similar topics:

Duplicate Emails

Resources:

Https://leetcode.com/discuss/61176/simple-solution-using-a-self-join

Https://leetcode.com/discuss/48403/my-answer-delete-duplicate-emails-with-double-nested-query

Leetcode all in one topic summary (continuous update ...)

[Leetcode] Delete Duplicate Emails Remove duplicate mailboxes