Lesson Description
Number rows within a partition.
Lesson 01
Lesson Description
Number rows within a partition.
Easy Project
Mini project: Build a compensation ranking dashboard query with department-level rank and delta from the top earner.
So far, every time you wanted a number that summarizes several rows, you reached for GROUP BY. That works, but it has a cost: the detail rows disappear. Ask for the highest salary per department and you get 9 rows, one per department, with no employee names attached.
Plenty of real questions need both the detail and the summary at the same time:
Window functions answer these. A window function calculates a value for each row by looking at a set of related rows (the "window"), and it keeps every row in the result. Nothing collapses. This lesson covers the three ranking functions you will use most: ROW_NUMBER(), RANK() and DENSE_RANK().
Every window function is followed by an OVER (...) clause. That clause is what makes it a window function.
function_name() OVER (
PARTITION BY column_a -- optional: split rows into independent groups
ORDER BY column_b -- the order used to hand out numbers
)
PARTITION BY works like the grouping part of GROUP BY, except the rows stay separate. Numbering restarts at 1 in every partition. Leave it out and the whole result is one big partition.ORDER BY inside OVER decides who gets number 1. It is independent of the ORDER BY at the end of the query, which only controls how the final rows are displayed.For ranking functions you almost always want an ORDER BY inside OVER. Without it, PostgreSQL has no basis for deciding which row comes first.
Start with the simplest case, no partition at all. In the HR sandbox, give every employee a sequence number by hire date:
SELECT first_name, last_name, hire_date,
ROW_NUMBER() OVER (ORDER BY hire_date) AS hire_seq
FROM employees
ORDER BY hire_seq
LIMIT 5;
| first_name | last_name | hire_date | hire_seq |
|---|---|---|---|
| Lex | De Haan | 2001-01-13 | 1 |
| Susan | Mavris | 2002-06-07 | 2 |
| Daniel | Faviet | 2002-08-16 | 3 |
| Nancy | Greenberg | 2002-08-17 | 4 |
| Den | Raphaely | 2002-12-07 | 5 |
Without the LIMIT you get all 40 employees, numbered 1 to 40. Lex De Haan is the longest-serving employee, so he gets 1. Every hire date in this table is unique, so the numbering is fully determined.
Now add PARTITION BY so each department gets its own ranking by salary:
SELECT first_name, last_name, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_pos
FROM employees
ORDER BY department_id, dept_pos;
This still returns 40 rows, but the numbering starts over at 1 for each of the 9 departments that have staff. Steven King is 1 in department 90 (Executive), Michael Hartstein is 1 in department 20 (Marketing), and so on.
Look closely at the IT department (60), though. Valli Pataballa and David Austin both earn 4,800. ROW_NUMBER() never gives out the same number twice, so one of them gets 3 and the other gets 4. Which one? PostgreSQL decides, and it is free to decide differently next time. That is the defining trait of ROW_NUMBER(): unique numbers, with ties broken arbitrarily unless you break them yourself.
The three functions only differ in how they treat ties. Running all three side by side on the IT and Executive departments makes the difference obvious:
SELECT first_name, last_name, department_id, salary,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense_rnk
FROM employees
WHERE department_id IN (60, 90)
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC)
ORDER BY department_id, salary DESC;
| first_name | department_id | salary | row_num | rnk | dense_rnk |
|---|---|---|---|---|---|
| Alexander | 60 | 9000 | 1 | 1 | 1 |
| Bruce | 60 | 6000 | 2 | 2 | 2 |
| Valli | 60 | 4800 | 3 | 3 | 3 |
| David | 60 | 4800 | 4 | 3 | 3 |
| Diana | 60 | 4200 | 5 | 5 | 4 |
| Steven | 90 | 24000 | 1 | 1 | 1 |
| Lex | 90 | 17000 | 2 | 2 | 2 |
| Neena | 90 | 17000 | 3 | 2 | 2 |
Eight rows come back (5 in IT, 3 in Executive). The interesting row is Diana Lorentz:
RANK() gives tied rows the same rank, then skips ahead. Two people share rank 3, so the next person is 5. Think of a race: if two runners tie for bronze, nobody finishes fourth.DENSE_RANK() also shares the rank but never leaves gaps, so Diana is 4.ROW_NUMBER() ignores ties entirely.The WINDOW w AS (...) clause is a handy PostgreSQL feature (it is standard SQL too) that lets you name a window once and reuse it, instead of repeating the same OVER (...) three times.
| Function | Tied rows get | Gaps after a tie? | Typical use |
|---|---|---|---|
ROW_NUMBER() | Different numbers | Not applicable | Pick exactly one row per group, deduplicate, paginate |
RANK() | Same number | Yes (1, 2, 2, 4) | Competition-style rankings, "top N including ties" |
DENSE_RANK() | Same number | No (1, 2, 2, 3) | "Nth highest distinct value" questions |
Window functions run after GROUP BY, which means you can rank aggregated values directly. In the hospital sandbox, rank doctors inside each department by how many appointments they have:
SELECT dp.name AS department, d.last_name,
COUNT(a.id) AS appointments,
RANK() OVER (
PARTITION BY dp.name
ORDER BY COUNT(a.id) DESC
) AS busy_rank
FROM doctors d
JOIN departments dp ON dp.id = d.department_id
LEFT JOIN appointments a ON a.doctor_id = d.id
GROUP BY dp.name, d.id, d.last_name
ORDER BY department, busy_rank;
You get 15 rows, one per doctor across 6 departments. In Emergency, Olivia Bennett ranks 1 with 7 appointments, Carlos Mendoza is 2 with 4, and Marcus Wright is 3 with 0. Wright still appears because of the LEFT JOIN you learned in the joins course, and COUNT(a.id) counts zero for him rather than one.
Notice that ORDER BY COUNT(a.id) DESC sits inside OVER. The grouping happens first, producing one row per doctor, and then the window function ranks those grouped rows.
This is the pattern you will write most often in practice. Which two products earned the most revenue in each store category?
WITH product_revenue AS (
SELECT c.name AS category, p.name AS product,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
GROUP BY c.name, p.name
),
ranked AS (
SELECT category, product, revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM product_revenue
)
SELECT category, product, revenue, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY category, rnk;
The result has 12 rows: two for each of the 6 categories. The Espresso Machine leads Home & Kitchen with 897.00, and the 4K Monitor leads Electronics with 698.00. The structure is a chain of CTEs, exactly like the ones from the previous course: compute, then rank, then filter.
Why the extra layer? Because you cannot filter on a window function in the same query that calculates it. The next section explains why.
Window functions are computed late in the logical order of a query:
FROM and JOINWHEREGROUP BYHAVINGOVER)SELECT list output, DISTINCTORDER BYLIMIT / OFFSETTwo consequences follow from this list:
WHERE runs before any ranking exists, so it cannot reference one. The same goes for HAVING and GROUP BY.WHERE changes which rows get ranked. If you filter to department_id IN (60, 90) first, the rankings are computed only over those rows, which is what you want in Example 3 but might surprise you elsewhere.You also saw in Example 4 that aggregates are already available by step 5, which is why RANK() OVER (ORDER BY COUNT(a.id) DESC) works.
SELECT first_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 3;
PostgreSQL refuses:
ERROR: window functions are not allowed in WHERE
Using the alias (WHERE rn <= 3) fails too, with column "rn" does not exist, because aliases from SELECT are not visible in WHERE. The fix is to compute the rank in a CTE or subquery and filter in the outer query, as in Example 5.
Asking for the top 2 earners per department with ROW_NUMBER() ... <= 2 returns 16 rows. With RANK() ... <= 2 you get 17, because in Executive, Lex De Haan and Neena Kochhar both earn 17,000. ROW_NUMBER() silently drops one of them, and which one depends on the execution plan. If a tie should include everyone, use RANK(). If you truly need exactly one row, keep ROW_NUMBER() but add a tiebreaker such as ORDER BY salary DESC, id so the result is repeatable.
RANK() OVER (PARTITION BY department_id) is legal, but with no ordering every row is a "tie", so every row gets rank 1. ROW_NUMBER() without an order hands out numbers in whatever order rows happen to arrive. The final ORDER BY of the query does not fix this; it only sorts the output after the numbers are assigned.
ROW_NUMBER(), RANK() and DENSE_RANK() behave the same way in SQL Server, Oracle, MySQL 8.0 and later, and SQLite 3.25 and later. Older MySQL (5.7 and before) has no window functions at all. The named WINDOW clause is supported in PostgreSQL, MySQL 8 and SQLite, but SQL Server only added it in SQL Server 2022.
Below this lesson are three exercises in the HR sandbox. The first asks you to number employees inside each department by salary with ROW_NUMBER(). The second asks for the same idea with RANK(), so watch what happens to the tied salaries in IT and Executive. The third asks you to keep only the top earner in each department, which means combining a ranking with a CTE and an outer filter. Run each query and check the row counts before you submit.
GROUP BY does.PARTITION BY restarts the calculation for each group; ORDER BY inside OVER decides the ranking order.ROW_NUMBER() never repeats, RANK() repeats and leaves gaps, DENSE_RANK() repeats with no gaps.WHERE, GROUP BY and HAVING, so filter on them in an outer query or CTE.ORDER BY whenever you need a stable ROW_NUMBER().Ranking functions number rows within a group while keeping every row, unlike GROUP BY. They answer top-N-per-group questions (the highest earner in each department, the best-selling product per category) and help remove duplicates.
Rank salaries inside each department
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense
FROM employees;With a tie, ROW_NUMBER still gives 1, 2; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2.
Top two earners per department
WITH ranked AS (
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, id) AS rn
FROM employees
)
SELECT department_id, first_name, salary
FROM ranked
WHERE rn <= 2;Rank in a CTE, then filter in the outer query. Adding id breaks ties so the result is stable.
RANK returns 3 (two people share first place) and DENSE_RANK returns 2.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name, last_name, department_id, salary, and a column dept_rank that ranks employees within each department by salary descending (use ROW_NUMBER).
No results yet
Run a query to see results here
Return first_name, last_name, department_id, salary, and salary_rank using RANK() (not ROW_NUMBER) within each department ordered by salary descending.
No results yet
Run a query to see results here
Using a CTE with ROW_NUMBER(), return only the highest-paid employee per department. Show first_name, last_name, department_id, and salary, ordered by department_id.
No results yet
Run a query to see results here