Technique
La dernière ligne par clé (dédoublonnage)
Garder une ligne par clé, la plus récente.
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 sans sous-requête, ou SELECT DISTINCT ON (customer_id) … ORDER BY customer_id, ordered_at DESC.
Ajoutez un critère de départage à l'ORDER BY pour que le choix soit 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 |