Reference · SQL (DuckDB)
Remove duplicates with a survivor rule in SQL (DuckDB)
Keep one row per key, chosen by a complete deterministic order, and report the others.
When to use it
Repeated records at the table's grain: re-sent rows, versions, the same customer from several systems.
How
row_number() over a partition by the key, ordered by the survivor rule; keep rank 1.
QUALIFY row_number() OVER (PARTITION BY order_id, line_id ORDER BY CAST(version AS BIGINT) DESC) = 1
- Rows with rank > 1 are the exceptions.
Pitfalls
- Deduplicating at the wrong grain.
- An incomplete order that leaves ties to chance.
- Comparing numbers or timestamps as text.
Lesson: Find the row grain before removing duplicates
Practise it: Deduplicate order lines at the right grain