Rank rows with a window function
GROUP BY answers questions by collapsing rows together - you lose the
individual rows and keep only the group totals. Sometimes you want the
opposite: a number computed across a set of rows, like a rank, but with
every original row still intact. That's what a window function does.
RANK() OVER (ORDER BY amount DESC) looks at every row's amount, works out
where it stands relative to all the others, and hands back a rank - but the
query still returns one row per order, not one row per rank. The ORDER BY
inside the parentheses only controls how the ranking is computed; it's
independent of a top-level ORDER BY at the end of a query (this lesson
doesn't need one at all).
Same tables as the subquery lesson.
Your task: return each order's item, amount, and its rank by amount
(highest first, as rank), without collapsing any rows.
You'll practice:
- Writing a window function with
OVER (ORDER BY ...)
- Seeing that a window function keeps one row per input row, unlike
GROUP BY