Pattern
Latest row per key (deduplication)
Keep one row per key, the most recent one.
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
FROM orders
) t
WHERE rn = 1;
DuckDB: QUALIFY rn = 1 without the subquery, or SELECT DISTINCT ON (customer_id) … ORDER BY customer_id, ordered_at DESC.
Add a tie-breaker to the ORDER BY so the choice is stable.
SELECT customer_id, order_id, ordered_at FROM orders WHERE customer_id IN (1, 2, 3) QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC, order_id DESC) = 1 ORDER BY customer_id;
| customer_id | order_id | ordered_at |
|---|---|---|
| 1 | 1865 | 2025-06-28 13:34:00 |
| 2 | 1268 | 2024-12-27 09:01:00 |
| 3 | 2487 | 2025-12-24 11:28:00 |