Leetcode SQL 50 -- 196. Delete Duplicate Emails

Question

Table name: Person

Column Name	Type
id	int
email	varchar

Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.
The final order of the table does not matter.

Explaination

Usually, people will simply replace the old table with the new table using SELECT to remove the duplicates.
The tricky part of this question is that you cannot create a new table, as the system only checks the original table Person.

Since we cannot use SELECT, the only way to filter out unwanted rows is to use WHERE or HAVING.

First of all, we can call the same table twice by giving them different name temporarily, so that we can compare between rows,

We need to keep the same email which has smaller id, so p.Id > q_Id, then we also need to nake sure that there are acutally duplicate email, so p.Email=q.Email

Solution

DELETE p from Person p, Person q where p.Id>q.Id AND q.Email=p.Email

---

More SQL 50 questions: here