SQL Deduplicate Rows with ROW_NUMBER

Number the duplicates, then keep only the first of each — a clean, reliable dedup pattern.

What & Why

The ROW_NUMBER deduplication pattern partitions by the duplicate key and numbers preferred survivors first. It removes repeated business-key rows from an analytical result without hiding which row the rule selected.

See How It Works

BUSINESS QUESTION

Keep the latest feedback row for each product feature in a one-row-per-feature review table.

id	user_id	feature_id	rating	nps_score	comment	created_at
1	101	1	5	9	Clear and useful	2024-01-25 12:15:00+00
2	102	2	4	NULL	Export worked well	2024-02-20 15:40:00+00
3	103	1	4	7	Helpful setup flow	2024-03-18 09:05:00+00
4	104	3	3	NULL	Needs clearer timing	2024-04-08 17:30:00+00

EXAMPLE QUERY

WITH numbered AS (
  SELECT
    id AS feedback_id,
    feature_id,
    rating,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY feature_id
      ORDER BY created_at DESC, id DESC
    ) AS duplicate_number
  FROM product.feedback
)
SELECT feedback_id, feature_id, rating, created_at
FROM numbered
WHERE duplicate_number = 1
ORDER BY feature_id;

RESULT — latest feedback row per feature

feedback_id	feature_id	rating	created_at
3	1	4	2024-03-18 09:05:00+00
2	2	4	2024-02-20 15:40:00+00
4	3	3	2024-04-08 17:30:00+00

Feature 1 has two feedback rows, so ROW_NUMBER keeps the newer feedback ID 3; features 2 and 3 each keep their only row.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice