Ranking Without Grouping
A window function computes a value across a set of rows related to the current row, but unlike GROUP BY it keeps every row. Ranking functions assign a position number within each window.
The Three Ranking Functions
The OVER clause defines the window, usually with PARTITION BY and ORDER BY:
- ROW NUMBER gives a unique sequential number, even for ties.
- RANK gives ties the same number but leaves gaps after them.
- DENSE RANK gives ties the same number with no gaps.
Top N Per Group
The classic use is finding the top three earners per department. You partition by department, order by salary descending, then keep rows where the rank is three or less. Because window functions run in SELECT, you wrap the query in a CTE or subquery to filter on the rank.
Why Partition Matters
PARTITION BY restarts the numbering for each group. Without it, the ranking runs across the whole result as one big window.
Key idea
Ranking window functions like ROW NUMBER, RANK, and DENSE RANK number rows within partitions while keeping all rows, enabling top N per group.