Lessons · SQL · the most recent one each

The latest row per group

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed DESC) numbers each customer's orders from newest; keep the rows numbered 1.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Each customer's most recent order, each film's latest review, each device's last reading: LIMIT 1 gives one row for the whole table, and this gives one per group.

How to think about it

Partition by the group, order within it so the row you want is first, number the rows in a CTE, then filter on the number. MAX in a subquery works too but breaks on ties and needs a second join.

Worked example

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

Number each customer's orders newest first, keep the first of each.

Your turn

Each film's highest-starred review.

ROW_NUMBER() OVER (PARTITION BY film_id ORDER BY stars ) AS rn

The trap

SELECT customer_id, MAX(placed), id FROM orders GROUP BY customer_id returns an id from some row, not the row with the max date. SQLite allows it; the answer is wrong.

Practise the most recent one each on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.