Latest row per entity: DISTINCT is not ordering

Problem and reasoning

DISTINCT removes rows only when every selected value is equal. If amount or timestamp differs, several orders for one customer remain, so DISTINCT cannot express “keep the latest order per customer.” Removing useful columns merely to satisfy DISTINCT loses information.

ROW_NUMBER partitions by customer_id and orders each partition by created_at DESC, making rn = 1 the latest record. Add a unique key such as id DESC when timestamps can tie so the selected row is repeatable.

Another option finds MAX(created_at) per customer and joins back, but tied timestamps can produce multiple rows again. Decide whether ties are allowed, which full-row fields are needed, and what compound index order supports the query.

Examples

Sample table

Table：orders

orders
customer_id	amount	created_at
101	1200	2026-01-03
102	900	2026-01-08
101	1500	2026-02-02
104	700	2026-02-15

Before / Not recommended

SELECT DISTINCT customer_id, amount, created_at FROM orders ORDER BY customer_id, created_at DESC;

Before result

result
customer_id	amount	created_at
101	1500	2026-02-02
101	1200	2026-01-03
102	900	2026-01-08
104	700	2026-02-15

After / Recommended

WITH ranked AS (SELECT customer_id, amount, created_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn FROM orders) SELECT customer_id, amount, created_at FROM ranked WHERE rn = 1 ORDER BY customer_id;

After result

result
customer_id	amount	created_at
101	1500	2026-02-02
102	900	2026-01-08
104	700	2026-02-15

Why it is better：DISTINCT keeps two different orders for customer 101; ROW_NUMBER uses timestamp and ID to select exactly the latest one.

Was this explanation clear?

Keep learning

Continue with related topics or compare database syntax in the SQL dialect translator.