How to remove duplicate rows in SQL: ROW_NUMBER, not DISTINCT

DISTINCT does not dedupe your table. It removes rows that are identical in every column, and real duplicates almost never are: the same order arrives twice with a different updated_at, and both versions stay. Deduplication is choosing one row per key with a rule. One page on finding duplicates, keeping the right row, and stopping them at the source. The print-ready A4 PDF is at the bottom.

The problem

• A duplicate is the same key on several rows.
• The cause: retries, replays, bad joins.
• DISTINCT only removes fully identical rows.

Find them first

• GROUP BY key HAVING COUNT(*) > 1 lists every duplicated key.
• Size the damage: total rows versus distinct keys.
• Define the key: decide what makes a row unique before deleting anything.

SELECT order_id, COUNT(*) AS copies
  FROM raw_orders
 GROUP BY order_id
HAVING COUNT(*) > 1;

Keep one row

• ROW_NUMBER() with PARTITION BY the key.
• ORDER BY updated_at DESC, then keep row number 1.
• Add a tie-breaker, or the winner is random.

Shortcuts

• QUALIFY rn = 1 on Snowflake, BigQuery, DuckDB and Databricks: no CTE needed.
• SELECT DISTINCT ON (key) is the Postgres way.
• GROUP BY with MAX on every column can mix values from two rows into one that never existed.

Fix the source

• A unique key: the table refuses duplicates.
• MERGE or upsert: update the existing row, do not append.
• An idempotent load: a retry adds nothing.

Keep the latest row per key

-- one row per order_id: the latest version
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC, ingest_id DESC
         ) AS rn
    FROM raw_orders
)
SELECT * FROM ranked WHERE rn = 1;
-- Snowflake, BigQuery: QUALIFY rn = 1, no CTE

Number the rows inside each key, newest first, and keep number one. ingest_id is the tie-breaker. Without a unique column at the end of the ORDER BY, two versions with the same updated_at can swap places from one run to the next.

Gotchas

• NULL in the key: a UNIQUE constraint lets NULLs repeat by default.
• Case and spaces: 'a1 ' is not 'A1'. Normalize the key first.
• DELETE in place: stage the deduplicated result and check it first.

The trap: dedupes that do not dedupe

Interview phrasing worth memorizing: DISTINCT removes identical rows. Deduplication is choosing one row per business key with a deterministic rule.