ROW_NUMBER() — Numbering Rows with Window Functions
title: “ROW_NUMBER() and Window Functions” part: 3 topic_number: 4 slug: “row-number” difficulty: “Advanced” prerequisites: “group-by, order-by” —
ROW_NUMBER() — Numbering Rows with Window Functions
What Is It?
ROW_NUMBER() assigns a unique sequential number to each row within a result set. It’s part of MySQL’s window functions — powerful tools that perform calculations across sets of rows without collapsing them (like GROUP BY does).
Real-world analogy: Like numbering pages in a book, or ranking runners in a race (1st, 2nd, 3rd…).
When you’d use it:
- Assign unique IDs to rows
- “Top N per group” queries
- Pagination (page 1, page 2, etc.)
- Deduplication
- Ranking and percentiles
Syntax Breakdown
1
2
3
4
ROW_NUMBER() OVER (
[PARTITION BY column(s)]
ORDER BY column(s) [ASC|DESC]
)
Components:
- ROW_NUMBER(): The function
- OVER: Defines the window (scope)
- PARTITION BY: Optional — creates separate numbering per group
- ORDER BY: Required — determines numbering order
Key difference from GROUP BY:
- GROUP BY collapses rows into groups
- Window functions keep all rows, add calculations
Basic Examples
Simple Row Numbering
1
2
3
4
5
6
7
SELECT
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
first_name,
last_name,
salary
FROM employees
ORDER BY rank;
Expected output:
| rank | first_name | last_name | salary |
|---|---|---|---|
| 1 | Sam | Clark | 95000.00 |
| 2 | Frank | Miller | 93000.00 |
| 3 | Henry | Moore | 92000.00 |
| 4 | Paul | Garcia | 87000.00 |
| 5 | Bob | Smith | 82000.00 |
| … | … | … | … |
Employees ranked by salary (highest to lowest).
Row Number Per Group (PARTITION BY)
1
2
3
4
5
6
7
8
9
10
11
SELECT
department_id,
first_name,
last_name,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS dept_rank
FROM employees
ORDER BY department_id, dept_rank;
Expected output:
| department_id | first_name | last_name | salary | dept_rank |
|---|---|---|---|---|
| 1 | Sam | Clark | 95000.00 | 1 |
| 1 | Paul | Garcia | 87000.00 | 2 |
| 1 | Alice | Johnson | 75000.00 | 3 |
| 2 | Frank | Miller | 93000.00 | 1 |
| 2 | Carol | Williams | 82000.00 | 2 |
| 3 | Bob | Smith | 82000.00 | 1 |
| 3 | Eve | Davis | 78000.00 | 2 |
| … | … | … | … | … |
Rank resets for each department (1, 2, 3… then 1, 2, 3… again).
Going Deeper
Top N Per Group
Find the top 2 highest-paid employees in each department:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH ranked_employees AS (
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.salary,
d.department_name,
ROW_NUMBER() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS dept_rank
FROM employees e
JOIN departments d ON e.department_id = d.department_id
)
SELECT
department_name,
first_name,
last_name,
salary,
dept_rank
FROM ranked_employees
WHERE dept_rank <= 2
ORDER BY department_name, dept_rank;
Expected output:
| department_name | first_name | last_name | salary | dept_rank |
|---|---|---|---|---|
| Engineering | Sam | Clark | 95000.00 | 1 |
| Engineering | Paul | Garcia | 87000.00 | 2 |
| Finance | Henry | Moore | 92000.00 | 1 |
| Finance | David | Brown | 66000.00 | 2 |
| Human Resources | Ivy | Anderson | 74000.00 | 1 |
| Human Resources | Jack | Anderson | 70000.00 | 2 |
| … | … | … | … | … |
Top 2 earners per department.
Pagination
1
2
3
4
5
6
7
8
9
10
11
12
-- Page 2 (rows 11-20)
WITH numbered_employees AS (
SELECT
ROW_NUMBER() OVER (ORDER BY last_name, first_name) AS row_num,
first_name,
last_name,
salary
FROM employees
)
SELECT *
FROM numbered_employees
WHERE row_num BETWEEN 11 AND 20;
Expected output:
| row_num | first_name | last_name | salary |
|---|---|---|---|
| 11 | Noah | Harris | 64000.00 |
| 12 | Quinn | Rodriguez | 67000.00 |
| 13 | Jack | Anderson | 70000.00 |
| … | … | … | … |
Page 2 of results (rows 11-20).
Deduplication
Find and remove duplicate emails (keep first occurrence):
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH ranked_emails AS (
SELECT
employee_id,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY employee_id
) AS email_occurrence
FROM employees
WHERE email IS NOT NULL
)
SELECT * FROM ranked_emails
WHERE email_occurrence > 1; -- These are duplicates!
Output: Employees with duplicate emails (2nd, 3rd occurrence, etc.).
To delete duplicates:
1
2
3
4
5
6
7
8
9
10
11
12
DELETE FROM employees
WHERE employee_id IN (
SELECT employee_id
FROM (
SELECT
employee_id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY employee_id) AS rn
FROM employees
WHERE email IS NOT NULL
) ranked
WHERE rn > 1
);
Alternate Ranking (Even/Odd Groups)
1
2
3
4
5
6
7
8
9
10
SELECT
first_name,
last_name,
ROW_NUMBER() OVER (ORDER BY hire_date) AS hire_order,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY hire_date) % 2 = 1 THEN 'Team A'
ELSE 'Team B'
END AS assigned_team
FROM employees
ORDER BY hire_order;
Expected output:
| first_name | last_name | hire_order | assigned_team |
|---|---|---|---|
| George | Jones | 1 | Team A |
| Ivy | Anderson | 2 | Team B |
| Frank | Miller | 3 | Team A |
| Alice | Johnson | 4 | Team B |
| … | … | … | … |
Alternating team assignments based on hire order.
Pause and Predict: What’s the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
Answer
**ROW_NUMBER()**: Always unique, sequential (1, 2, 3, 4, 5...) - Ties get different numbers **RANK()**: Gaps after ties (1, 2, 2, 4, 5...) - Two people tied for 2nd → both get 2, next person gets 4 (skips 3) **DENSE_RANK()**: No gaps (1, 2, 2, 3, 4...) - Two people tied for 2nd → both get 2, next person gets 3 **Example:** | salary | ROW_NUMBER() | RANK() | DENSE_RANK() | |--------|--------------|--------|--------------| | 95000 | 1 | 1 | 1 | | 93000 | 2 | 2 | 2 | | 93000 | 3 | 2 | 2 | | 87000 | 4 | 4 | 3 | We'll cover RANK() in the next topic!Watch Out — Common Mistakes
Mistake #1: Forgetting ORDER BY
1
2
3
4
-- ERROR
SELECT ROW_NUMBER() OVER (PARTITION BY department_id) AS rn
FROM employees;
-- Error: ROW_NUMBER requires ORDER BY!
Fix:
1
2
3
-- • CORRECT
SELECT ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS rn
FROM employees;
Mistake #2: Using ROW_NUMBER in WHERE
1
2
3
4
5
-- DOESN'T WORK
SELECT first_name, salary
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 5;
-- Error: window functions not allowed in WHERE
Fix: Use CTE or subquery:
1
2
3
4
5
6
7
8
9
10
11
-- • CORRECT
WITH ranked AS (
SELECT
first_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
SELECT first_name, salary
FROM ranked
WHERE rn <= 5;
Mistake #3: Misunderstanding PARTITION BY
1
2
3
4
5
6
7
-- Without PARTITION BY: numbering across entire table
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees;
-- With PARTITION BY: separate numbering per department
SELECT ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;
These produce VERY different results!
Edge Case Spotlight
Multiple Window Functions
1
2
3
4
5
6
7
8
9
10
SELECT
first_name,
last_name,
salary,
department_id,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY overall_rank;
Three rankings in one query! Each OVER clause is independent.
Named Windows (Cleaner Syntax)
1
2
3
4
5
6
7
8
SELECT
first_name,
salary,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
AVG(salary) OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);
Define window once, reuse it.
Try This
Exercise 1 (Guided)
Assign row numbers to projects ordered by team size (descending), then by project name. Show project_name, team_size, and row_num.
Hint
Join projects with employee_projects, GROUP BY and COUNT for team size, then ROW_NUMBER in outer query.Exercise 2 (Independent)
Find the 3rd highest-paid employee overall (not per department). Show first_name, last_name, salary, and rank.
Hint
Use ROW_NUMBER() OVER (ORDER BY salary DESC), filter WHERE rn = 3 in CTE.Exercise 3 (Challenge)
Create a “paired mentorship” system: for each department, pair employees in order of hire date (1st with 2nd, 3rd with 4th, etc.). Show mentor_name, mentee_name, department_name, and pair_number.
Hint
Use ROW_NUMBER() to number employees per dept by hire_date. Self-join where one person has odd number, other has even (rn+1). Calculate pair_number.Answer Key
Exercise 1 Answer
```sql WITH project_sizes AS ( SELECT p.project_name, COUNT(ep.employee_id) AS team_size FROM projects p LEFT JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_id, p.project_name ) SELECT project_name, team_size, ROW_NUMBER() OVER (ORDER BY team_size DESC, project_name) AS row_num FROM project_sizes ORDER BY row_num; ``` **Expected output:** | project_name | team_size | row_num | |--------------|-----------|---------| | Website Redesign | 4 | 1 | | Data Pipeline | 3 | 2 | | Mobile App | 3 | 3 | | API Development | 2 | 4 | | ... | ... | ... | Projects ranked by team size.Exercise 2 Answer
```sql WITH ranked_salaries AS ( SELECT first_name, last_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees ) SELECT first_name, last_name, salary, rank FROM ranked_salaries WHERE rank = 3; ``` **Expected output:** | first_name | last_name | salary | rank | |------------|-----------|---------|------| | Henry | Moore | 92000.00 | 3 | Third highest-paid employee.Exercise 3 Answer
```sql WITH numbered_employees AS ( SELECT e.employee_id, e.first_name, e.last_name, e.department_id, d.department_name, ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS dept_order FROM employees e JOIN departments d ON e.department_id = d.department_id ) SELECT CONCAT(e1.first_name, ' ', e1.last_name) AS mentor_name, CONCAT(e2.first_name, ' ', e2.last_name) AS mentee_name, e1.department_name, CEIL(e1.dept_order / 2.0) AS pair_number FROM numbered_employees e1 JOIN numbered_employees e2 ON e1.department_id = e2.department_id AND e1.dept_order % 2 = 1 -- Odd number (mentor) AND e2.dept_order = e1.dept_order + 1 -- Next person (mentee) ORDER BY e1.department_name, pair_number; ``` **Expected output:** | mentor_name | mentee_name | department_name | pair_number | |-------------|-------------|-----------------|-------------| | Alice Johnson | Paul Garcia | Engineering | 1 | | Sam Clark | (no pair) | Engineering | 2 | | Frank Miller | Carol Williams | Marketing | 1 | | ... | ... | ... | ... | Mentorship pairs within each department. **Note:** If odd number of employees in a dept, last person has no pair.Quick Recap
• ROW_NUMBER() assigns sequential numbers to rows
• OVER clause defines the window scope
• PARTITION BY creates separate numbering per group
• ORDER BY (required) determines numbering order
• Window functions don’t collapse rows (unlike GROUP BY)
• Use CTEs to filter by row number (can’t use in WHERE directly)
• Useful for: top N per group, pagination, deduplication
• Different from RANK() / DENSE_RANK() in tie handling
Up Next
Next topic: RANK() and DENSE_RANK() → part3_05_rank.md
Type ‘next’ when ready to continue!