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.