ROW_NUMBER function in SQL - Step-by-Step Execution

Concept Flow - ROW_NUMBER function

Start Query Execution

↓

Scan Table Rows

↓

Apply ORDER BY inside PARTITION

↓

Assign ROW_NUMBER starting at 1

↓

Return Rows with ROW_NUMBER

↓

End

The ROW_NUMBER function assigns a unique number to each row in the result set based on the specified order, restarting numbering for each partition if used.

Execution Sample

SQL

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

This query assigns a row number to each employee within their department, ordered by their name.

Execution Table

Step	Action	Input Rows	Order Applied	Row Number Assigned	Output Row
1	Scan employees table	[{name: 'Alice', department: 'HR'}, {name: 'Bob', department: 'IT'}, {name: 'Carol', department: 'HR'}, {name: 'Dave', department: 'IT'}]	N/A	N/A	All rows scanned
2	Partition by department	All rows	N/A	N/A	Partitions: HR and IT
3	Order each partition by name	HR: Alice, Carol; IT: Bob, Dave	HR: Alice < Carol; IT: Bob < Dave	N/A	Ordered partitions
4	Assign ROW_NUMBER starting at 1 in each partition	Ordered partitions	N/A	HR: Alice=1, Carol=2; IT: Bob=1, Dave=2	Rows with row numbers assigned
5	Return final result	Rows with row numbers	N/A	N/A	[{name: 'Alice', department: 'HR', rn: 1}, {name: 'Carol', department: 'HR', rn: 2}, {name: 'Bob', department: 'IT', rn: 1}, {name: 'Dave', department: 'IT', rn: 2}]

💡 All rows processed and numbered within their partitions.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Input Rows	N/A	[All rows scanned]	[Partitions created]	[Partitions ordered]	[Rows with row numbers]
Partitions	N/A	HR, IT groups	HR ordered by name, IT ordered by name	N/A	N/A
Row Number (rn)	N/A	N/A	N/A	Assigned starting at 1 per partition	Assigned per row

Key Moments - 3 Insights

Why does ROW_NUMBER restart numbering for each department?

Because the PARTITION BY clause groups rows by department, and ROW_NUMBER starts counting from 1 within each group, as shown in execution_table row 4.

What happens if we omit PARTITION BY?

ROW_NUMBER will assign unique numbers across the entire result set without restarting, numbering all rows sequentially, unlike the partitioned numbering in execution_table.

How does ORDER BY inside OVER() affect row numbering?

ORDER BY defines the order in which rows are numbered within each partition, as seen in execution_table row 3 where rows are ordered by name before numbering.

Visual Quiz - 3 Questions

Look at the execution_table, what is the row number assigned to 'Carol' in the HR department?

A 1

B 3

C 2

D 0

💡 Hint

Check row 4 in execution_table where row numbers are assigned per partition.

At which step does the query order rows within each department?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look at execution_table row 3 describing ordering inside partitions.

If PARTITION BY is removed, how would the row numbers change?

A Row numbers are assigned randomly

B Row numbers count continuously over all rows

C Row numbers restart for each department

D No row numbers are assigned

💡 Hint

Refer to key_moments explanation about PARTITION BY effect on numbering.

Concept Snapshot

ROW_NUMBER() assigns a unique sequential number to rows.
Use OVER() with ORDER BY to define numbering order.
PARTITION BY restarts numbering for each group.
Numbers start at 1 for each partition or entire set.
Useful for ranking or pagination in queries.

Full Transcript

The ROW_NUMBER function in SQL assigns a unique number to each row in the result set. It works by scanning the table rows, optionally grouping them into partitions using PARTITION BY, then ordering each partition by specified columns. After ordering, it assigns numbers starting at 1 within each partition. The final output includes the original rows plus the assigned row numbers. This helps to rank rows or paginate results. If PARTITION BY is omitted, numbering continues across all rows without restarting. The ORDER BY inside OVER() controls the order of numbering. This visual trace shows each step from scanning rows, partitioning, ordering, numbering, to returning the final result.

Practice

1. What does the ROW_NUMBER() function do in SQL?

easy

A. Counts the total number of rows in a table.

B. Filters rows based on a condition.

C. Calculates the sum of values in a column.

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

Solution

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

   The ROW_NUMBER() function assigns a unique sequential integer to rows in the result set, starting at 1.

2. Step 2: Compare with other options

   Counting rows, summing values, or filtering rows are done by other SQL functions like COUNT(), SUM(), or WHERE clause, not ROW_NUMBER().

3. Final Answer:

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

4. Quick Check:

   ROW_NUMBER() = unique row numbers [OK]

Hint: ROW_NUMBER() gives each row a unique number starting at 1 [OK]

Common Mistakes:

• Confusing ROW_NUMBER() with COUNT()
• Thinking ROW_NUMBER() filters rows
• Assuming ROW_NUMBER() sums values

2. Which of the following is the correct syntax to assign row numbers ordered by salary in descending order?

easy

A. SELECT ROW_NUMBER() FROM employees ORDER BY salary DESC;

B. SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

C. SELECT ROW_NUMBER() ORDER BY salary DESC FROM employees;

D. SELECT ROW_NUMBER(salary DESC) FROM employees;

Solution

1. Step 1: Identify correct ROW_NUMBER() syntax

   The correct syntax uses ROW_NUMBER() OVER (ORDER BY column) to assign numbers ordered by a column.

2. Step 2: Check each option

   Only SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; correctly uses OVER (ORDER BY salary DESC). The other options omit the OVER clause, pass arguments directly to ROW_NUMBER(), or misplace the ORDER BY clause.

3. Final Answer:

   SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; -> Option B

4. Quick Check:

   ROW_NUMBER() needs OVER with ORDER BY [OK]

Hint: ROW_NUMBER() must use OVER() with ORDER BY inside [OK]

Common Mistakes:

• Omitting OVER() clause
• Placing ORDER BY outside OVER()
• Passing column directly to ROW_NUMBER()

3. Given the table sales with columns region and amount, what will this query return?

SELECT region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales;

medium

A. The total count of sales per region.

B. A unique row number for all sales ignoring region, ordered by amount ascending.

C. A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first.

D. The sum of amounts per region.

Solution

1. Step 1: Understand PARTITION BY effect

   Using PARTITION BY region restarts the row numbering for each region group separately.

2. Step 2: Understand ORDER BY inside ROW_NUMBER()

   Ordering by amount DESC means the highest amount in each region gets row number 1, next highest 2, and so on.

3. Final Answer:

   A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first. -> Option C

4. Quick Check:

   ROW_NUMBER() with PARTITION BY = numbering per group [OK]

Hint: PARTITION BY restarts numbering per group [OK]

Common Mistakes:

• Ignoring PARTITION BY and numbering all rows globally
• Confusing ORDER BY direction
• Thinking ROW_NUMBER() counts rows

4. Identify the error in this query:

SELECT name, ROW_NUMBER() ORDER BY salary FROM employees;

medium

A. Missing OVER() clause after ROW_NUMBER().

B. ORDER BY cannot be used in SELECT clause.

C. ROW_NUMBER() cannot be used without GROUP BY.

D. salary column does not exist.

Solution

1. Step 1: Check ROW_NUMBER() syntax

   The ROW_NUMBER() function requires an OVER() clause specifying ordering or partitioning.

2. Step 2: Analyze the query

   The query uses ROW_NUMBER() without OVER(), which causes a syntax error. ORDER BY inside SELECT is invalid without OVER().

3. Final Answer:

   Missing OVER() clause after ROW_NUMBER(). -> Option A

4. Quick Check:

   ROW_NUMBER() must have OVER() [OK]

Hint: ROW_NUMBER() always needs OVER() clause [OK]

Common Mistakes:

• Forgetting OVER() after ROW_NUMBER()
• Placing ORDER BY outside OVER()
• Assuming ROW_NUMBER() works without ordering

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 * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2;

B. SELECT *, ROW_NUMBER() FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department);

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

D. SELECT department, salary FROM employees ORDER BY salary DESC LIMIT 2;

Solution

1. Step 1: Use ROW_NUMBER() with PARTITION BY department

   To get top 2 per department, assign row numbers restarting for each department ordered by salary descending.

2. Step 2: Filter rows with row number less than or equal to 2

   Wrap the query as a subquery and filter where rn <= 2 to get top 2 employees per department.

3. Final Answer:

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

4. Quick Check:

   ROW_NUMBER() + PARTITION + filter = top N per group [OK]

Hint: Use ROW_NUMBER() with PARTITION BY and filter rn <= N [OK]

Common Mistakes:

• Not using PARTITION BY to group by department
• Filtering on rn in the same SELECT without subquery
• Using LIMIT without partitioning