Deduplication

Production data has duplicates. A frontend retried a request, a background job ran twice, an ETL load was rerun without an idempotency key. Whatever the cause, the job is the same: collapse the duplicates without losing information.

SELECT DISTINCT is the wrong tool most of the time, because it only removes rows that are identical in every selected column — and real duplicates differ in something (a fresh id, a slightly later timestamp). So before any SQL, you must decide two things: what counts as a duplicate, and which copy to keep. Take this orders table, where the same purchase was recorded twice:

id	user_id	product_id	amount
1	7	4	900
2	7	4	900
3	9	3	9

Rows 1 and 2 are the same (user_id, product_id) — a duplicate group. SELECT DISTINCT * would keep both, because their ids differ.

Pattern 1 — ROW_NUMBER, then keep rn = 1

The flexible pattern: number the rows within each “should be unique” group, ordered by which one wins, and keep the first:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id, product_id ORDER BY id) AS rn
  FROM   orders
)
SELECT id, user_id, product_id, amount
FROM   ranked
WHERE  rn = 1;

id	user_id	product_id	amount
1	7	4	900
3	9	3	9

PARTITION BY user_id, product_id defines the duplicate group, and ORDER BY id picks the survivor — here, the lowest id, the first one inserted. Row 2 loses and drops out. This keeps the whole row, which is its advantage over the next pattern.

Pattern 2 — GROUP BY with an aggregate

When you only need the keys plus a summary, GROUP BY is tighter — but it collapses the row to keys and aggregates, so you cannot get the full surviving row back this way:

SELECT user_id, COUNT(*) AS n_rows, MAX(amount) AS max_amount
FROM   orders
GROUP BY user_id;

user_id	n_rows	max_amount
7	2	900
9	1	9

Use ROW_NUMBER when…	Use GROUP BY when…
You need the full row	You only need keys + aggregates
There’s a clear winner (latest, lowest, highest)	Duplicates are exact, either is fine
You may want to inspect the losers	You just want unique values + stats

Look before you delete

Before running anything destructive, find the duplicates with the standard “show me the dupes” query:

SELECT user_id, product_id, COUNT(*) AS dup_count
FROM   orders
GROUP BY user_id, product_id
HAVING COUNT(*) > 1;

user_id	product_id	dup_count
7	4	2

Practice

Quick check

0/3

Q1Two events share user_id and event_id but have different timestamps. What does SELECT DISTINCT * do?

Keeps one of themKeeps both — the timestamps differ, so the rows aren't identicalErrorsKeeps the first

Check answer

Q2You want the latest event per user_id and need the full row, all columns. Which pattern?

GROUP BY user_id with MAX(event_ts)SELECT DISTINCT user_id, MAX(event_ts)ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) in a CTE, then WHERE rn = 1A correlated subquery with MAX

Check answer

Q3Best thing to write before a deduplication query?

An index on every columnA comment stating the dedup rule (unique keys + tiebreaker order)A backup of the tableA LIMIT clause

Check answer