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
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.
Part by part
- PARTITION BY sku
- Groups the rows by the column that defines a duplicate. Getting this wrong is the difference between deduplicating and deleting most of your data.
- ORDER BY updated_at DESC
- Puts the newest row in each group first, so row number 1 is the one worth keeping.
- WHERE rn = 1
- Keeps exactly one row per sku. Changing it to rn > 1 gives you the rows a delete would remove, which is the safer thing to run first.
Common mistakes
SELECT DISTINCT * FROM imports;DISTINCT only removes rows that are identical in every column. Two records for the same sku with different timestamps are not identical, so nothing is deduplicated and the problem looks solved.
ORDER BY updated_atWithout DESC this keeps the oldest row in each group rather than the newest — the exact opposite of the intent, and completely silent about it.
GROUP BY skuGrouping collapses the rows and forces every other column into an aggregate, so you end up with MAX() of each column independently. That produces a row assembled from several different records, which never existed in the source.
Questions
How do I check this before deleting anything?
Change the filter to rn > 1 and run it as a SELECT. That lists precisely the rows a delete would remove, so you can inspect them and confirm the count is what you expect before anything is destroyed.
What if updated_at is the same on both rows?
The tie is broken arbitrarily and may differ between runs, which means a delete could remove a different row each time. Add a deterministic tiebreak such as the primary key so the outcome is repeatable.
Can I turn this into a DELETE?
Yes, and the syntax varies by database — Postgres uses DELETE with a USING clause or a CTE, MySQL a multi-table delete. Whichever you use, run the rn > 1 SELECT first and take a backup, because there is no undo.
Should I prevent duplicates instead?
Where you can, yes. A unique constraint on sku stops the problem at the source, and an upsert lets an import update the existing row rather than adding another. Deduplication is a repair, not a fix.