## TL;DR
Rank rows with `row_number() OVER (PARTITION BY key ORDER BY updated_at DESC)` and delete where the rank is greater than 1. The ORDER BY decides the survivor, so make it explicit and deterministic.

```text
sql delete duplicate rows keep latest
```

## Use this when
- A table has duplicate rows and you need one survivor per key
- The survivor must be the latest (or earliest) by a timestamp
- The delete must be safe and verifiable

## Not for this skill when
- You only need to find duplicates, not delete them
- You are choosing between rank functions conceptually
- Pandas merge keys mismatch

## Steps

1. First, look at the duplicates without deleting anything:

```sql
SELECT user_id, count(*)
FROM events
GROUP BY user_id
HAVING count(*) > 1
ORDER BY count(*) DESC
LIMIT 20;
```
Expected output: the worst duplicate keys and their counts. This tells you the scale of the delete before you run it.

2. Preview exactly which rows would survive and which would die:

```sql
SELECT *, row_number() OVER (
  PARTITION BY user_id ORDER BY updated_at DESC, id DESC
) AS rn
FROM events
WHERE user_id IN (SELECT user_id FROM events GROUP BY user_id HAVING count(*) > 1)
LIMIT 20;
```
Expected output: rn = 1 marks survivors, rn > 1 marks deletions. The tiebreak (`id DESC`) makes the survivor deterministic.

3. Run the delete. Postgres version with ctid:

```sql
DELETE FROM events WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid, row_number() OVER (
      PARTITION BY user_id ORDER BY updated_at DESC, id DESC
    ) AS rn
    FROM events
  ) s WHERE rn > 1
);
```
Expected output: the deleted row count. On databases without ctid, join the delete to the ranked subquery on the primary key instead.

4. Verify the table is clean and add the constraint that prevents recurrence:

```sql
SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id HAVING count(*) > 1) d;
ALTER TABLE events ADD CONSTRAINT events_user_id_uniq UNIQUE (user_id);
```
Expected output: `0` duplicates, then the constraint builds. If the constraint fails, the delete missed something; do not skip the verification.

5. For very large tables, delete in batches to avoid one giant transaction:

```sql
-- repeat until 0 rows affected:
DELETE FROM events WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid, row_number() OVER (
      PARTITION BY user_id ORDER BY updated_at DESC, id DESC) AS rn
    FROM events
  ) s WHERE rn > 1 LIMIT 10000
);
```
Expected output: 10000 or fewer rows per batch. Keeps each transaction small, lets autovacuum keep up, and is interruptible.

## Variant phrasings

### sql remove duplicates keep max date
The row_number() pattern with ORDER BY date DESC (steps 2-3). "Keep latest" is just the ORDER BY direction.

### postgres delete duplicate rows
ctid identifies physical rows for the delete (step 3). Without a primary key it is the only reliable row handle.

### sql deduplicate table keep first
ORDER BY created_at ASC keeps the earliest instead. The pattern is identical; only the survivor rule changes.

## Why it happens
Duplicates come from missing unique constraints, double-ingested batches, or application retries. SQL has no "delete duplicates" primitive, so the standard approach is ranking rows within each duplicate group and deleting every rank above 1.

## Edge cases
- NULL keys: GROUP BY treats NULLs as one group, so NULL-keyed rows dedupe against each other; decide if that is correct.
- Foreign keys referencing the table: deleting rows can violate or cascade; check dependents first.
- The unique constraint in step 4 locks the table while it validates; use a NOT VALID + VALIDATE split for zero-downtime on huge tables.
- If "latest" is ambiguous (ties on updated_at), the id tiebreak decides; without it the survivor is arbitrary.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_LUowvc46dNX9Am93SrWd8g