DeleteDuplicateEmails.sql

-- Source : https://leetcode.com/problems/delete-duplicate-emails
-- Author : Hamza Mogni
-- Date   : 2022-06-17

/***************************************************************************************************** 
 *
 * Table: Person
 * 
 * +-------------+---------+
 * | Column Name | Type    |
 * +-------------+---------+
 * | id          | int     |
 * | email       | varchar |
 * +-------------+---------+
 * id is the primary key column for this table.
 * Each row of this table contains an email. The emails will not contain uppercase letters.
 * 
 * Write an SQL query to delete all the duplicate emails, keeping only one unique email with the 
 * smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.
 * 
 * After running your script, the answer shown is the Person table. The driver will first compile and 
 * run your piece of code and then show the Person table. The final order of the Person table does not 
 * matter.
 * 
 * The query result format is in the following example.
 * 
 * Example 1:
 * 
 * Input: 
 * Person table:
 * +----+------------------+
 * | id | email            |
 * +----+------------------+
 * | 1  | john@example.com |
 * | 2  | bob@example.com  |
 * | 3  | john@example.com |
 * +----+------------------+
 * Output: 
 * +----+------------------+
 * | id | email            |
 * +----+------------------+
 * | 1  | john@example.com |
 * | 2  | bob@example.com  |
 * +----+------------------+
 * Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.
 ******************************************************************************************************/


DELETE 
    p 
FROM
    Person p, Person tmp
WHERE p.email = tmp.email AND
      p.id > tmp.id
;