SQL deduplication — the flickering latest-row bug

Start with the basics

Deduplication is picking one row per entity when a table has several — the latest subscription per customer, one row per order after a messy load.

The usual tool is a window function: rank the rows per entity by some order, then keep the first. The order decides which row wins.

It reads as solved, and it is — as long as the ordering reaches a unique tiebreaker. When two rows tie on the ordering value, 'first' becomes a coin flip.

One that works

SELECT cust_id, plan FROM (
  SELECT cust_id, plan,
    ROW_NUMBER() OVER (PARTITION BY cust_id
      ORDER BY updated_at DESC) rn
  FROM subs
) t WHERE rn = 1;

Distinct timestamps make 'latest' well-defined — the 10:05 row wins.

To keep one row per entity you rank the rows and take the first — but the ranking must break every tie, or 'first' is arbitrary.

When to use it

• ·Reach for dedup when a table has more than one row per entity and you want exactly one.
• ·Rank per entity with a window function, then keep the first — and make the order end in a unique key.
• ·Prefer a window over joining back on a MAX timestamp; the window is cheaper and avoids re-introducing ties.

That rule — the order must reach a unique tiebreaker — is exactly what a shared timestamp breaks. When two rows tie on 'latest', the query picks one arbitrarily, and the answer flickers between runs. Here is the day that bites.

See it happen

The 'latest row' that flickers — a tie with no tiebreaker

A customer has two subscription rows updated at the exact same instant. You take the latest. Watch how the query picks one arbitrarily — a different one on a different run.

SELECT cust_id, plan FROM (
  SELECT cust_id, plan, ROW_NUMBER() OVER (
    PARTITION BY cust_id
    ORDER BY updated_at DESC) rn
  FROM subs
) t WHERE rn = 1;

SELECT cust_id, plan FROM (
  SELECT cust_id, plan, ROW_NUMBER() OVER (
    PARTITION BY cust_id
    ORDER BY updated_at DESC, sub_id DESC) rn
  FROM subs
) t WHERE rn = 1;

! The customer's plan flickers between refreshes of the same table — the bug that can't be reproduced, because both answers are consistent with the query.

The rest of the family

More ways a dedup picks the wrong row

Self-check

How to spot it in your own SQL

• →A 'latest record' result changes between runs with no data change.
• →You dedup by ranking on a timestamp that can repeat.
• →You join back on a MAX timestamp to find the latest row.
• →You deduped after a join and the counts still look inflated.

What people miss

Deduplication looks done the moment the row count drops. The bug hides in the ties: a 'latest' with no unique tiebreaker is a coin flip that lands the same in dev and differently in prod — impossible to reproduce, easy to ship.

Go deeper