Técnica
Última fila por clave (deduplicación)
Conservar una fila por clave, la más reciente.
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 sin la subconsulta, o SELECT DISTINCT ON (customer_id) … ORDER BY customer_id, ordered_at DESC.
Añada un criterio de desempate al ORDER BY para que la elección sea estable.
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 |