# Finding and Removing Duplicates — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/a-dedup

> Keep one row per key.

## Define the key, choose the survivor

First define what makes rows duplicates (here, the email ignoring case) and inspect them with GROUP BY ... HAVING count(*) > 1. Then keep one survivor per key, usually the earliest or most complete row, using ROW_NUMBER() OVER (PARTITION BY key ORDER BY preference) and delete rows with rn > 1. Add a **unique constraint** (here a unique index on lower(email)) afterwards so duplicates cannot return.

## Deduplicating sign-ups by email, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Two keys are duplicated; the later rows 3 and 5 are deleted (RETURNING shows them), leaving one row per email.

```sql
SELECT lower(email) AS email, count(*) FROM signups
GROUP BY lower(email) HAVING count(*) > 1 ORDER BY 1;

DELETE FROM signups WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created, id) AS rn
    FROM signups) t
  WHERE rn > 1)
RETURNING id, email;

SELECT id, email FROM signups ORDER BY id;
```

Output:

```
  email  | count 
---------+-------
 a@x.com |     2
 b@x.com |     2
(2 rows)

 id |  email  
----+---------
  3 | A@x.com
  5 | b@x.com
(2 rows)

 id |  email  
----+---------
  1 | a@x.com
  2 | b@x.com
  4 | c@x.com
(3 rows)
```

## Preview in a transaction

Run the DELETE inside BEGIN, check RETURNING and counts, then COMMIT or ROLLBACK.

**Quiz:** What prevents duplicates from reappearing after cleanup?

- [x] A unique constraint or unique index on the key
- [ ] An ORDER BY clause
- [ ] A view
- [ ] Running the cleanup weekly only

*Answer:* A unique constraint or unique index on the key. Let the database enforce it.