Window Functions: RANK() and PARTITION BY

Overview

Window functions compute a value across a set of related rows — a "window" — without collapsing those rows into a single output row the way GROUP BY does. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) gives every employee a rank relative to their own department's salaries, while still returning one row per employee.

This is the tool for "top N per group" reports, leaderboards, and any situation where you need both the individual row and its position within a group — something a plain GROUP BY can't give you, since it discards the individual rows.

The Query

Step-by-Step Breakdown

1. RANK() OVER (...) — the RANK() function assigns a rank number to each row based on the ordering defined inside OVER (...).
2. PARTITION BY department_id — resets the ranking for each department independently. Without this, RANK() would rank every employee against the entire company at once.
3. ORDER BY salary DESC (inside OVER) — defines what "rank" means here: the highest-paid employee in each department gets rank 1.
4. The outer SELECT — every original employee row is still present; salary_rank is just an extra computed column alongside it. Unlike GROUP BY, no rows are lost or merged.
5. ORDER BY department_id, salary_rank (outside) — this is a separate, ordinary ORDER BY on the final result set, controlling display order rather than the ranking logic itself.

Variations

Compare each employee's salary to their department's average, side by side:

Here there's no ORDER BY inside the OVER (...) clause at all, because AVG over a partition doesn't depend on row order — every employee in a department sees the same average, computed across their whole department's partition.

Common Mistakes

• Confusing window functions with GROUP BY. A window function keeps every input row; GROUP BY collapses rows into one per group. Reach for a window function specifically when you need the detail rows and an aggregate context alongside them.
• Forgetting PARTITION BY entirely. Without it, the window covers the whole result set, so RANK() ranks every employee company-wide instead of within their department — a subtle bug that only shows up once you check the numbers.
• Putting a window function in the WHERE clause. Window functions are computed after WHERE filtering, so you can't reference salary_rank there directly — you'd need to wrap the query in a CTE or subquery and filter on the outside.
• Assuming RANK() always starts at 1 for the whole table. It restarts at 1 for every partition, so rank 1 appears once per department here, not once overall.