Deduplication & latest-record

Deterministic latest via complete ORDER BY, DISTINCT vs GROUP BY vs QUALIFY, dedup before/after join.

• ★'Latest' without a tiebreaker

  ROW_NUMBER() ORDER BY updated_at DESC with tied timestamps picks a row arbitrarily — the ORDER BY must reach a unique key.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE subs(sub_id INT, cust_id INT, plan VARCHAR, updated_at TIMESTAMP); INSERT INTO subs VALUES (1,7,'pro', TIMESTAMP '2024-05-01 10:00:00'),(2,7,'free', TIMESTAMP '2024-05-01 10:00:00');

  The trap

  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;

  0 rows

  The fix

  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;

  7

  free

  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.

• DISTINCT vs GROUP BY vs DISTINCT ON vs QUALIFY

  Each dedups differently and costs differently — DISTINCT ON and QUALIFY keep a chosen row, DISTINCT/GROUP BY collapse.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE members(cust INT, city VARCHAR); INSERT INTO members VALUES (7,'Rome'),(7,'Oslo');

  The trap

  SELECT DISTINCT * FROM members ORDER BY cust, city;

  7	Oslo
  7	Rome

  The fix

  SELECT cust, city FROM (SELECT cust, city, ROW_NUMBER() OVER (PARTITION BY cust ORDER BY city DESC) AS rn FROM members) WHERE rn = 1;

  7

  Rome

  SELECT DISTINCT * kept both of customer 7's rows — DISTINCT dedups whole output rows, so a differing city leaves both; entity dedup needs a chosen winner (ROW_NUMBER = 1), which DISTINCT can't make.

• Dedup before vs after join

  Deduping after a join can't undo fan-out already created — dedup at the source grain (P06 crossover).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE stock(item VARCHAR, qty INT, donor_id INT); INSERT INTO stock VALUES ('rice',3,1),('beans',2,2); CREATE TABLE donors(donor_id INT, name VARCHAR); INSERT INTO donors VALUES (1,'Ada'),(1,'Ada'),(2,'Ben');

  The trap

  SELECT SUM(s.qty) AS total FROM stock s JOIN donors d ON d.donor_id = s.donor_id;

  8

  The fix

  SELECT SUM(s.qty) AS total FROM stock s JOIN (SELECT DISTINCT donor_id, name FROM donors) d ON d.donor_id = s.donor_id;

  5

  A double-entered donor row fanned the stock — the joined SUM read 8, not 5, because rice matched the duplicated donor twice; deduping the donor list before the join fixes it.

• MAX-timestamp-then-join anti-pattern

  Joining back on MAX(updated_at) re-introduces ties and a second scan — a window is usually cheaper and safer.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE fittings(cust INT, ts INT); INSERT INTO fittings VALUES (7,5),(7,3),(9,2);

  The trap

  SELECT cust, ts FROM fittings WHERE ts = (SELECT MAX(ts) FROM fittings) ORDER BY cust;

  7

  5

  The fix

  SELECT cust, ts FROM fittings f WHERE ts = (SELECT MAX(ts) FROM fittings f2 WHERE f2.cust = f.cust) ORDER BY cust;

  7	5
  9	2

  The last-fitting report dropped customer 9 — an uncorrelated MAX(ts) subquery finds the table's single latest row, not each customer's latest, so only the globally-latest entity survived the filter.

• Soft duplicates and survivorship

  Hard duplicates are identical rows; soft duplicates are one entity with conflicting values, resolved by a per-column survivorship policy written as SQL.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE horses(horse VARCHAR, workshop VARCHAR, paint VARCHAR, acquired INT, inspected INT); INSERT INTO horses VALUES ('star','A','teal',1990,2020),('star','B','navy',1995,2023);

  The trap

  SELECT horse, paint, acquired FROM (SELECT horse, paint, acquired, ROW_NUMBER() OVER (PARTITION BY horse ORDER BY inspected DESC) AS rn FROM horses) WHERE rn = 1;

  star

  navy

  1995

  The fix

  SELECT h.horse, (SELECT paint FROM horses h2 WHERE h2.horse = h.horse ORDER BY inspected DESC LIMIT 1) AS paint, MIN(acquired) AS acquired FROM horses h GROUP BY h.horse;

  star

  navy

  1990

  Taking the whole latest-inspection row set the horse's acquired year to 1995 — soft duplicates need a per-column survivorship policy, so the newest paint comes from the latest inspection but the earliest acquisition is its own MIN, not the winner row's.