Window functions (ROW_NUMBER) in MySQL - Step-by-Step Execution

Concept Flow - Window functions (ROW_NUMBER)

Start with table data

↓

Define window partition/order

↓

Apply ROW_NUMBER() over window

↓

Assign row numbers per partition/order

↓

Return result with row numbers

ROW_NUMBER() assigns a unique number to each row within a partition of the result set, ordered by specified columns.

Execution Sample

MySQL

SELECT name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY name) AS rn
FROM employees;

Assigns a row number to each employee within their department, ordered by name.

Execution Table

Step	Row Data (name, department)	Partition	Order by name	ROW_NUMBER assigned
1	(Alice, Sales)	Sales	Alice	1
2	(Bob, Sales)	Sales	Bob	2
3	(Charlie, HR)	HR	Charlie	1
4	(David, HR)	HR	David	2
5	(Eve, IT)	IT	Eve	1
6	(Frank, IT)	IT	Frank	2
7	(Grace, IT)	IT	Grace	3
8	(Heidi, Sales)	Sales	Heidi	3
Exit	All rows processed			ROW_NUMBER assigned per partition

💡 All rows processed, each assigned a row number within their department ordered by name.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	Final
ROW_NUMBER (Sales)	0	1	2						3	3
ROW_NUMBER (HR)	0			1	2					2
ROW_NUMBER (IT)	0					1	2	3		3

Key Moments - 3 Insights

Why does ROW_NUMBER restart at 1 for each department?

Because ROW_NUMBER() is used with PARTITION BY department, it resets counting for each department group as shown in execution_table rows 1-2 (Sales), 3-4 (HR), and 5-7 (IT).

What determines the order of row numbers within each partition?

The ORDER BY clause inside OVER() defines the order. Here, rows are ordered by name alphabetically within each department, as seen in the 'Order by name' column.

What happens if we remove PARTITION BY?

ROW_NUMBER() would assign unique numbers across the entire result set without resetting, numbering all rows from 1 to total rows.

Visual Quiz - 3 Questions

Look at the execution_table, what is the ROW_NUMBER assigned to Heidi in Sales?

A 2

B 3

C 1

D 4

💡 Hint

Check the row with Heidi in the execution_table under 'ROW_NUMBER assigned' column.

At which step does the ROW_NUMBER for HR department reach 2?

A Step 3

B Step 5

C Step 4

D Step 2

💡 Hint

Look at execution_table rows for HR partition and their assigned ROW_NUMBER.

If we remove PARTITION BY, how would the ROW_NUMBER for Alice change?

A It would be 1

B It would be 4

C It would be 8

D It would be 0

💡 Hint

Without partition, ROW_NUMBER counts from 1 for the whole table, so first row is 1.

Concept Snapshot

ROW_NUMBER() assigns unique row numbers per partition.
Use PARTITION BY to reset numbering per group.
ORDER BY defines numbering order.
Without PARTITION BY, numbering is global.
Syntax: ROW_NUMBER() OVER (PARTITION BY col ORDER BY col).

Full Transcript

This visual trace shows how the ROW_NUMBER() window function works in MySQL. Starting with a table of employees, the function assigns a unique number to each row within each department group, ordered by employee name. The flow begins by defining partitions by department and ordering by name. Then, ROW_NUMBER() counts rows starting at 1 for each department. The execution table shows each row processed with its assigned number. Variables track row numbers per department as they increment. Key moments clarify why numbering restarts per partition and how ordering affects results. The quiz tests understanding of row numbers assigned at specific steps and the effect of removing partitioning. The snapshot summarizes syntax and behavior for quick reference.

Practice

1. What does the ROW_NUMBER() function do in MySQL window functions?

easy

A. Assigns a unique sequential number to each row in the result set.

B. Calculates the sum of values in a column.

C. Filters rows based on a condition.

D. Joins two tables based on a key.

Solution

1. Step 1: Understand the purpose of ROW_NUMBER()

   ROW_NUMBER() assigns a unique number to each row in the order specified by ORDER BY.

2. Step 2: Differentiate from other functions

   It does not sum, filter, or join; it only numbers rows uniquely.

3. Final Answer:

   Assigns a unique sequential number to each row in the result set. -> Option A

4. Quick Check:

   ROW_NUMBER() = unique row numbering [OK]

Hint: ROW_NUMBER() gives each row a unique number in order [OK]

Common Mistakes:

• Confusing ROW_NUMBER() with aggregate functions like SUM()
• Thinking ROW_NUMBER() filters rows
• Assuming ROW_NUMBER() joins tables

2. Which of the following is the correct syntax to assign row numbers partitioned by department and ordered by salary DESC?

easy

A. ROW_NUMBER(PARTITION BY department ORDER BY salary DESC)

B. ROW_NUMBER() PARTITION BY department ORDER BY salary DESC

C. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)

D. ROW_NUMBER() BY department ORDER BY salary DESC

Solution

1. Step 1: Recall correct ROW_NUMBER() syntax

   The correct syntax uses OVER() with PARTITION BY and ORDER BY inside parentheses.

2. Step 2: Identify correct option

   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) correctly uses ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC).

3. Final Answer:

   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) -> Option C

4. Quick Check:

   Correct syntax includes OVER() with PARTITION BY and ORDER BY [OK]

Hint: ROW_NUMBER() always needs OVER() with clauses inside [OK]

Common Mistakes:

• Omitting OVER() keyword
• Placing PARTITION BY outside parentheses
• Using incorrect keywords order

3. Given the table Employees with columns id, department, salary, what will be the output of this query?

SELECT id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees;

Assuming data:
id | department | salary
1 | Sales | 5000
2 | Sales | 7000
3 | HR | 6000
4 | HR | 5500

medium

A. [{id:2, department:'Sales', salary:7000, rn:2}, {id:1, department:'Sales', salary:5000, rn:1}, {id:3, department:'HR', salary:6000, rn:2}, {id:4, department:'HR', salary:5500, rn:1}]

B. [{id:1, department:'Sales', salary:5000, rn:2}, {id:2, department:'Sales', salary:7000, rn:1}, {id:3, department:'HR', salary:6000, rn:1}, {id:4, department:'HR', salary:5500, rn:2}]

C. [{id:1, department:'Sales', salary:5000, rn:1}, {id:2, department:'Sales', salary:7000, rn:2}, {id:4, department:'HR', salary:5500, rn:1}, {id:3, department:'HR', salary:6000, rn:2}]

D. [{id:1, department:'Sales', salary:5000, rn:1}, {id:2, department:'Sales', salary:7000, rn:1}, {id:3, department:'HR', salary:6000, rn:1}, {id:4, department:'HR', salary:5500, rn:1}]

Solution

1. Step 1: Partition rows by department

   Rows are grouped into Sales and HR departments separately.

2. Step 2: Order each partition by salary descending and assign row numbers

   Within Sales: id 2 (7000) gets rn=1, id 1 (5000) rn=2; Within HR: id 3 (6000) rn=1, id 4 (5500) rn=2.

3. Final Answer:

   [{id:1, department:'Sales', salary:5000, rn:2}, {id:2, department:'Sales', salary:7000, rn:1}, {id:3, department:'HR', salary:6000, rn:1}, {id:4, department:'HR', salary:5500, rn:2}] -> Option B

4. Quick Check:

   Partition by department + order by salary DESC = correct row numbers [OK]

Hint: Partition groups restart numbering; order controls row number sequence [OK]

Common Mistakes:

• Ignoring PARTITION BY and numbering globally
• Ordering ascending instead of descending
• Assigning same row number to multiple rows

4. Identify the error in this query:

SELECT id, ROW_NUMBER() PARTITION BY department ORDER BY salary FROM Employees;

medium

A. Missing OVER() keyword before PARTITION BY clause.

B. ORDER BY cannot be used inside ROW_NUMBER().

C. PARTITION BY should be after ORDER BY.

D. ROW_NUMBER() cannot be used with SELECT.

Solution

1. Step 1: Check ROW_NUMBER() syntax

   ROW_NUMBER() requires OVER() before PARTITION BY and ORDER BY clauses.

2. Step 2: Identify missing OVER()

   The query misses OVER(), causing syntax error.

3. Final Answer:

   Missing OVER() keyword before PARTITION BY clause. -> Option A

4. Quick Check:

   ROW_NUMBER() must have OVER() [OK]

Hint: Always use OVER() with ROW_NUMBER() [OK]

Common Mistakes:

• Omitting OVER() keyword
• Placing PARTITION BY outside OVER()
• Misordering clauses inside OVER()

5. You want to select the top 2 highest paid employees per department from the Employees table. Which query correctly uses ROW_NUMBER() to achieve this?

hard

A. SELECT id, department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM Employees WHERE rn <= 2;

B. SELECT *, ROW_NUMBER() FROM Employees WHERE rn <= 2 PARTITION BY department ORDER BY salary DESC;

C. SELECT id, department, salary FROM Employees ORDER BY salary DESC LIMIT 2 PARTITION BY department;

D. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees) AS t WHERE rn <= 2;

Solution

1. Step 1: Use ROW_NUMBER() with PARTITION BY department and ORDER BY salary DESC

   This assigns row numbers restarting at 1 for each department, ordered by salary descending.

2. Step 2: Filter rows where row number is 2 or less

   Wrap the window function query as a subquery and filter with WHERE rn <= 2 to get top 2 per department.

3. Final Answer:

   SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees) AS t WHERE rn <= 2; -> Option D

4. Quick Check:

   Use subquery with ROW_NUMBER() and filter rn <= 2 [OK]

Hint: Use subquery with ROW_NUMBER() and filter rn <= 2 [OK]

Common Mistakes:

• Filtering rn in outer query without subquery
• Using LIMIT with PARTITION BY incorrectly
• Omitting PARTITION BY causing global numbering