Remove Duplicates

Source data — Pattern 11: Interview SQL Patterns

All queries run against the shared employees table (see schema.sql). Topics 71 and 72 introduce an orders table to solve consecutive records and gaps-and-islands problems. Topics 73 and 74 introduce a monthly_sales table to demonstrate PIVOT and UNPIVOT operations.

---

What it does

Deletes duplicate rows while keeping one record.

Problem

Delete duplicate employee records.

DELETE FROM employees
WHERE employee_id NOT IN (
  SELECT MIN(employee_id)
  FROM employees
  GROUP BY email
);

Result