SQL: get the latest row per group

Question

For each customer_id I need the most recent order from orders(id, customer_id, created_at, total).

Dmitri Vasko · Accepted Answer

In Postgres the shortest form is DISTINCT ON:
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;
Portable SQL uses a window function:
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

Priya Raghunathan · Answer

An index on (customer_id, created_at DESC) makes both of these fast.

SQL: get the latest row per group

Related topics

Suggested topics