Keep only the latest row per group (dedupe with ROW_NUMBER)

This isn’t the same problem as the delete-duplicates recipe. That one removes exact repeat rows. This one keeps the single most recent row per key from a table where every row is legitimately different — a change log, an events table, a slowly-changing dimension — and it’s a SELECT, not a DELETE.

-- MySQL 8+, PostgreSQL, SQLite 3.25+ — no QUALIFY, wrap ROW_NUMBER in a subquery/CTE
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_events
)
SELECT * FROM ranked WHERE rn = 1;

-- BigQuery, Snowflake, DuckDB — QUALIFY filters the window function inline
SELECT *
FROM customer_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;

To persist the result as its own “latest” table instead of re-running the window function every time, INSERT INTO ... SELECT works identically in all six dialects:

DELETE FROM customers_latest;

INSERT INTO customers_latest
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_events
) t
WHERE rn = 1;

PARTITION BY picks the key, ORDER BY ... DESC picks the winner. Swap ORDER BY updated_at DESC for whatever “latest” means for your data — ORDER BY version DESC or ORDER BY created_at DESC, id DESC. Always add a unique tiebreaker (like id) after the main sort column: two rows sharing the exact same updated_at otherwise get an arbitrary winner, which can flip between runs. And note rn = 1 (not rn <= 1) — for the top N survivors per group instead of just one, see the top-N-per-group recipe, which is the same pattern with <= N.