Dedupe Latest Row
Keep one row per key, typically the most recent. A specific case of top-N-per-group with N = 1. Used constantly against event streams, audit logs, and slowly-changing dimensions where the source has multiple rows per entity and you want the current state.
Syntax #
sql
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY key_col
ORDER BY ts_col DESC, id DESC
) AS rn
FROM table_name
)
SELECT *
FROM ranked
WHERE rn = 1;Example #
Loading SQL editor...
Warning
A timestamp alone is almost never a unique sort key. Two events can share the same created_at down to the millisecond, and then ROW_NUMBER picks one arbitrarily, so the "latest" row drifts between runs. Always add a stable secondary sort (usually the primary key): ORDER BY created_at DESC, id DESC. The same trap applies to "latest by version number," "most recent by score," and any other dedup where ties are possible.