Skip to Content

Deduplication with DISTINCT ON & ROW_NUMBER

When to use

Collapse repeated records down to one row per logical entity (latest action per user, per-day snapshot, latest embedding per chunk).

Analogy

DISTINCT ON is the rule ‘pick the first row for each group, sorting however you want’ — Postgres-specific but elegant.

Data-flow diagram

  rows         DISTINCT ON (user_id)        result
  +-----+      ORDER BY user_id, ts DESC    --------
  | r1  |  ->  +--> take FIRST row        ->  kept: r3 (latest ts)
  | r2  |      |                            dropped: r1, r2
  | r3  |
  +-----+

Deep explanation

DISTINCT ON keeps the FIRST row of each group after sorting — a Postgres-specific gem that often replaces a whole window-function subquery. ROW_NUMBER() OVER (…) is the cross-dialect equivalent and is more flexible, especially when you want to dedup across multiple ties or split into ‘kept vs discarded’ tables. For ML feature pipelines, dedup is essential: duplicates inflate training-set size, distort embeddings, and break cross-validation folds.

Examples

Example 1

-- 9a: DISTINCT ON — keep most recent event per user
SELECT DISTINCT ON (user_id) user_id, ts, payload
  FROM events
 ORDER BY user_id, ts DESC;

DISTINCT ON is the cheapest dedup pattern in Postgres — the planner does a sort-then-emit-first-row per group.

Example 2

-- 9b: ROW_NUMBER — explicit dedup with 'kept' flag for auditing
SELECT user_id, ts, payload,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
  FROM events;
 
-- to delete dupes while archiving:
DELETE FROM events e
 USING (SELECT ctid, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
          FROM events) ranked
 WHERE e.ctid = ranked.ctid
   AND ranked.rn > 1;

ROW_NUMBER gives you an explicit duplicate-rank column you can branch on: keep rn=1, archive rn > 1.

Example 3

-- 9c: per-day snapshot dedup keep latest version per (id, day)
SELECT DISTINCT ON (id, snapshot_date) id, snapshot_date, value
  FROM snapshots
 ORDER BY id, snapshot_date, version DESC;

Per-day snapshots become trivial: DISTINCT ON over (id, snapshot_date) with ORDER BY version DESC.

Common mistake

Using GROUP BY + ARRAY_AGG for dedup — it works but loses row shape. DISTINCT ON keeps the original row, which is what you almost always want. Another trap: DELETE ... USING without ctid or PK match — accidentally deletes the latest instead of the dupes.

Key takeaway

DISTINCT ON for one-liner dedup; ROW_NUMBER when you need explicit rank or partitioned deletes; always test dedup against a known-multi-row fixture; dedup before training to avoid duplicate-feature bias.

Production Failure Playbook

Failure scenario 1: dedup-lost-correct-row

Failure scenario 2: dedup-on-non-unique-tie-breaker