Skip to main content
Window Functions
1 / 4
0%

ROW_NUMBER and RANK

0/3 solved3 to unlock next
Locked

Lesson 01

ROW_NUMBER and RANK

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.

Why window functions exist

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:

  • "List every employee, and next to each one show where they rank on pay inside their own department."
  • "For each product category in the store, which two products brought in the most revenue?"
  • "Which doctor in each hospital department handles the most appointments?"

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().

The syntax

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.

Example 1: number every row

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_namelast_namehire_datehire_seq
LexDe Haan2001-01-131
SusanMavris2002-06-072
DanielFaviet2002-08-163
NancyGreenberg2002-08-174
DenRaphaely2002-12-075

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.

Example 2: restart the count in every department

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.

Example 3: ROW_NUMBER vs RANK vs DENSE_RANK

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_namedepartment_idsalaryrow_numrnkdense_rnk
Alexander609000111
Bruce606000222
Valli604800333
David604800433
Diana604200554
Steven9024000111
Lex9017000222
Neena9017000322

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.

FunctionTied rows getGaps after a tie?Typical use
ROW_NUMBER()Different numbersNot applicablePick exactly one row per group, deduplicate, paginate
RANK()Same numberYes (1, 2, 2, 4)Competition-style rankings, "top N including ties"
DENSE_RANK()Same numberNo (1, 2, 2, 3)"Nth highest distinct value" questions

Example 4: rank the result of an aggregate

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.

Example 5: top N per group

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.

How PostgreSQL evaluates it

Window functions are computed late in the logical order of a query:

  1. FROM and JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. Window functions (OVER)
  6. SELECT list output, DISTINCT
  7. ORDER BY
  8. LIMIT / OFFSET

Two 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.

Common mistakes

Filtering on a window function in WHERE

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.

Using ROW_NUMBER when ties matter

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.

Leaving out ORDER BY inside OVER

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.

Other databases

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.

Practice

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.

Key takeaways

  • A window function adds a calculated column to every row without collapsing rows the way 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.
  • Window functions run after WHERE, GROUP BY and HAVING, so filter on them in an outer query or CTE.
  • Add a unique tiebreaker to ORDER BY whenever you need a stable ROW_NUMBER().

ROW_NUMBER and RANK: in depth

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.

Worked examples

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.

Common mistakes

  • Choosing ROW_NUMBER when ties should share a place (or RANK when you need exactly N rows). Decide which behaviour the question needs.
  • Forgetting PARTITION BY, which ranks across the whole table instead of within each group.
  • Trying to use the ranking in WHERE of the same query; wrap it in a CTE or subquery first.

Other databases

  • ROW_NUMBER, RANK and DENSE_RANK are available in PostgreSQL, SQL Server, Oracle, SQLite 3.25+ and MySQL 8.0+.
  • Some databases (Snowflake, BigQuery, DuckDB) support QUALIFY rn <= 2 to filter without a subquery; PostgreSQL does not.
Check yourself: Salaries in a department are 9000, 9000, 8000. What do RANK and DENSE_RANK return for 8000?

RANK returns 3 (two people share first place) and DENSE_RANK returns 2.

Concept check

Quick check: ROW_NUMBER and RANK

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Rank employees within each department by salary

Enterprise Workforce AnalyticshardPostgreSQL

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).

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

RANK with salary ties within department

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, department_id, salary, and salary_rank using RANK() (not ROW_NUMBER) within each department ordered by salary descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Top earner in each department

Enterprise Workforce AnalyticseasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Window Functions
1 / 4
0%

ROW_NUMBER and RANK

0/3 solved3 to unlock next
Locked