Muster
Die neueste Zeile pro Schlüssel (Deduplizierung)
Eine Zeile pro Schlüssel behalten, die neueste.
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 ohne die Unterabfrage, oder SELECT DISTINCT ON (customer_id) … ORDER BY customer_id, ordered_at DESC.
Fügen Sie dem ORDER BY ein Unterscheidungskriterium hinzu, damit die Wahl stabil ist.
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 |