Window functions without fear

GROUP BY collapses many rows into one. Window functions do something different: they calculate over a group of rows but keep every row. That way you can put a rank or a running total next to each piece of data.

A place for every runner

SELECT name, category, time,
       RANK() OVER (PARTITION BY category ORDER BY time) AS place
FROM results;

• •OVER (...) turns the function into a window function.
• •PARTITION BY splits the windows: here, one ranking per category.
• •ORDER BY says how to sort within each window.

ROW_NUMBER, RANK and DENSE_RANK

If two runners tie, ROW_NUMBER gives them different numbers (1 and 2), RANK gives them the same one and skips the next (1, 1, 3), and DENSE_RANK skips none (1, 1, 2).

Running totals

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

🦉 Tip: Need “the best of each group”? Number the rows with ROW_NUMBER in a subquery and keep the rows where the number is 1.