PatternSQL
Deduplicate, keeping the latest row
Collapse duplicate records to the freshest one: rank each group by recency and keep rank 1 — the standard cleanup for sync jobs and imports.
Last updated
Pattern
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY sku
ORDER BY updated_at DESC
) AS rn
FROM imports
)
WHERE rn = 1;Why it’s written this way
Same machinery as top-N-per-group with N = 1: partition on the column that defines "the same record", order by the column that defines "newest", keep the first of each group. Every re-import and every sync conflict resolves to one row per sku.
SELECT DISTINCT cannot do this — it needs rows to be identical in every column, and duplicates that matter are precisely the ones that differ somewhere.
Edge cases to know
- →Two rows with the same updated_at tie arbitrarily — add a second ORDER BY key (an id) to make the winner deterministic.
- →NULL updated_at sorts last under DESC in most engines (first in others) — decide explicitly with NULLS LAST if those rows exist.
- →This SELECTs the deduplicated view; actually deleting the losers is a separate, more careful statement built on the same ranking.