Lessons · SQL · the top row per group

The whole row that holds the maximum

MAX gives the value, not its row. ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) numbers each group's rows from the biggest; 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

The biggest order with its id, the best score with the player's name, the latest reading with its sensor: interviews ask for the row, and MAX alone cannot return it.

How to think about it

Number the rows inside each group in the order you care about, in a CTE, then filter for rn = 1. Add a tie-breaker to the ORDER BY so two equal values cannot both be number one.

Worked example

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

Each customer's biggest order, with its id; id breaks ties.

Your turn

The cheapest product per city of stock.

ROW_NUMBER() OVER (PARTITION BY city ORDER BY price ) AS rn

The trap

SELECT customer_id, id, MAX(amount) FROM orders GROUP BY customer_id returns an id from an arbitrary row in SQLite. It runs, and it is wrong.

Practise the top row per group on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.