Deduplication

Production data has duplicates. A frontend retried a request, a background job ran twice, or 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. It only removes rows that are identical in every selected column. 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