Lesson 36 / 37 · Windows

Keeping one row per group with ROW_NUMBER

Number the rows inside each partition, then keep number 1. Unlike GROUP BY, the surviving row keeps all its columns. Hiro has two orders of 189; id in the ORDER BY decides which one wins, otherwise the choice would be arbitrary.

The query

SELECT id, customer_id, amount
FROM orders
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) = 1

Try = 2: each customer's second-biggest order instead, and customers with a single order disappear.

Next: Capstone: the revenue is too high · All lessons