SQL Duplicates: DISTINCT, ROW_NUMBER, and Safe Winner Rules

Choose what counts as a duplicate

DISTINCT removes repeated projected rows. ROW_NUMBER can select one record per business key. Neither function knows whether two equal-looking records are accidental duplicates or separate legitimate events. This PostgreSQL 16 tutorial defines a winner rule, proves the output on tied and missing timestamps, then rehearses a deletion using temporary data that is rolled back.

Write the rule before the query

Name the business key, define a total winner order, state how NULL keys behave, preview losing IDs, and prevent new duplicates at the appropriate source. These choices determine correctness. Adding DISTINCT until a result has fewer rows can discard valid events while leaving an inflated aggregate untouched.

Use customer ID, not a convenient-looking value

Our fictional contacts feed can contain several versions for a customer. The report wants one row per known customer_id. An email address is a value that can change, not part of this chosen identity. The newest non-NULL updated_at wins; equal timestamps break toward the larger unique id. A missing customer_id means unresolved identity, so each such row remains separate. That policy deliberately does not group all NULL IDs into one customer.

DISTINCT depends on the projection

WITH visits(visit_id,customer_id) AS (VALUES (1,7),(2,7),(3,8))
SELECT DISTINCT customer_id FROM visits ORDER BY customer_id;

customer_id
7
8

This is correct for “which customers visited?” It does not mean visit 2 is an invalid duplicate of visit 1. Including visit_id in the SELECT would preserve all three visits. Aggregating visits after deduplicating customer_id would answer a different question. Distinctness applies to all selected expressions together.

Make ties and missing timestamps explicit

WITH contacts(id,customer_id,updated_at,email) AS (
 VALUES (1,1,TIMESTAMP '2026-01-01','[email protected]'),
        (2,1,TIMESTAMP '2026-01-01','[email protected]'),
        (3,2,TIMESTAMP '2026-01-02','[email protected]'),
        (4,2,NULL::timestamp,'[email protected]'),
        (5,NULL,TIMESTAMP '2026-01-03','[email protected]'),
        (6,NULL,TIMESTAMP '2026-01-04','[email protected]')
), ranked AS (
 SELECT *,ROW_NUMBER() OVER (
  PARTITION BY customer_id, CASE WHEN customer_id IS NULL THEN id END
  ORDER BY updated_at DESC NULLS LAST,id DESC
 ) AS rn FROM contacts
)
SELECT id,customer_id,email FROM ranked WHERE rn=1 ORDER BY id;

id | customer_id | email
2 | 1 | [email protected]
3 | 2 | [email protected]
5 | NULL | [email protected]
6 | NULL | [email protected]

Rows 1 and 2 tie on time, so id 2 wins. Row 4 has no known update time, so it loses to row 3. Rows 5 and 6 have separate partitions because the conditional second partition key is their unique id. If unresolved identities should instead be excluded, return them to a separate exception report; do not silently pick one. If timestamps and IDs do not reflect authority, choose another verified source priority rather than assuming “latest” means correct.

ROW_NUMBER assigns unique row numbers within a partition, but ties in its ordering need a deliberate tie-breaker. The primary-key assumption for id makes our order total. See the PostgreSQL window-function reference.

Keep the rule when changing syntax

PostgreSQL DISTINCT ON can also pick one row per group, with its expressions matching the leftmost ORDER BY expressions. The remaining order still determines which row wins. QUALIFY is available in some other SQL dialects, but is not PostgreSQL 16 syntax. The subquery pattern here keeps the ranking and filtering explicit; consult the target engine before translating it. See PostgreSQL’s DISTINCT ON rules.

Rehearse deletion on temporary rows

Run this entire script in a disposable PostgreSQL learning database. It materializes the losing IDs, checks the exact expected set, deletes only those IDs, displays the survivors, and rolls back. It does not touch an application contacts table.

BEGIN;
CREATE TEMP TABLE dedup_demo (
 id integer PRIMARY KEY, customer_id integer, updated_at timestamp
) ON COMMIT DROP;
INSERT INTO dedup_demo VALUES
 (1,1,'2026-01-01'),(2,1,'2026-01-01'),(3,2,'2026-01-02'),
 (4,2,NULL),(5,NULL,'2026-01-03'),(6,NULL,'2026-01-04');
CREATE TEMP TABLE dedup_losers ON COMMIT DROP AS
SELECT id FROM (
 SELECT id,ROW_NUMBER() OVER (
  PARTITION BY customer_id,CASE WHEN customer_id IS NULL THEN id END
  ORDER BY updated_at DESC NULLS LAST,id DESC
 ) AS rn FROM pg_temp.dedup_demo
) ranked WHERE rn>1;
SELECT id AS proposed_delete FROM pg_temp.dedup_losers ORDER BY id;
DO $$ BEGIN
 IF (SELECT ARRAY_AGG(id ORDER BY id) FROM pg_temp.dedup_losers)
    IS DISTINCT FROM ARRAY[1,4] THEN
  RAISE EXCEPTION 'Unexpected candidate IDs';
 END IF;
END $$;
WITH deleted AS (
 DELETE FROM pg_temp.dedup_demo d USING pg_temp.dedup_losers l
 WHERE d.id=l.id RETURNING d.id
)
SELECT id AS deleted_id FROM deleted ORDER BY id;
SELECT id AS surviving_id FROM pg_temp.dedup_demo ORDER BY id;
ROLLBACK;

proposed_delete: 1, 4
deleted_id: 1, 4
surviving_id: 2, 3, 5, 6

A production cleanup needs more than copying this fixture with a different table name. Concurrent writes can change the winner between review and deletion. Establish a maintenance window or a suitable locking/version-check strategy, preserve required audit evidence, review referencing rows and triggers, and abort if the approved candidate set changes. A preview SELECT alone is not a concurrency guarantee. PostgreSQL DELETE supports the USING and RETURNING clauses used here; see its DELETE reference.

Do not delete source records to repair a join

A customer with several orders naturally repeats after a customer-to-orders join. If the requested result is one row per customer, aggregate the orders or use an existence test before joining other detail. Those order rows can all be valid. Deleting them to reduce a report’s row count would corrupt the underlying history.

Enforce the correct invariant

If one current record per known customer is the actual storage contract, enforce it with a suitable constraint or current-record design after the existing data is reconciled. If the table is an append-only history, repeated customer IDs are expected; enforce unique event/version IDs instead. Idempotent ingestion keys should identify a retried source event without collapsing separate events with equal values.

Related practice

Compare the source and result grain before deduplicating any join. A one-row aggregate can hide inflated measures even when its final output contains no duplicate rows.

FAQ

Is DISTINCT always slow? No. Inspect the actual plan and data volume; correctness comes first. Should I deduplicate before summing? Only when repeated records violate the measure’s intended event key. Equal amounts on distinct valid orders must both contribute.