ROW_NUMBER() in SQL
🔢 ROW_NUMBER() is a SQL window function that assigns a unique sequential number to each row in a result set. Numbering starts at 1 and increases by one for every row within the specified window.
📖 What is ROW_NUMBER()?
ROW_NUMBER() generates a unique number for each row based on the order defined in the ORDER BY clause of the OVER() window. Even if multiple rows contain identical values, each row receives a different row number.
Information
ROW_NUMBER() is commonly used for ranking, pagination, identifying duplicate rows, and selecting the first or last record within groups.
🎯 Why Use ROW_NUMBER()?
ROW_NUMBER() is useful whenever every row needs a unique position within a result set or partition.
- 📌 Generate sequential row numbers.
- 📌 Rank records uniquely.
- 📌 Implement pagination.
- 📌 Remove duplicate rows.
- 📌 Select top records from each group.
📋 Sample Table
| EmployeeID | EmployeeName | Department | Salary |
|---|---|---|---|
| 101 | Alice | Sales | 60000 |
| 102 | Bob | Sales | 55000 |
| 103 | Charlie | IT | 75000 |
| 104 | David | IT | 70000 |
| 105 | Emma | HR | 50000 |
📝 Basic Syntax
ROW_NUMBER() Syntax
SELECT
column1,
column2,
ROW_NUMBER() OVER (
ORDER BY column_name
) AS RowNum
FROM table_name;The ORDER BY clause inside OVER() determines the sequence in which row numbers are assigned.
💡 Example: Number All Employees
Assign Sequential Row Numbers
SELECT
EmployeeName,
Salary,
ROW_NUMBER() OVER (
ORDER BY Salary DESC
) AS RowNum
FROM Employees;Employees are numbered from the highest salary to the lowest salary.
📊 Example Output
| RowNum | EmployeeName | Salary |
|---|---|---|
| 1 | Charlie | 75000 |
| 2 | David | 70000 |
| 3 | Alice | 60000 |
| 4 | Bob | 55000 |
| 5 | Emma | 50000 |
💡 Example: Restart Numbering by Department
Use PARTITION BY to restart numbering for each group.
ROW_NUMBER() with PARTITION BY
SELECT
EmployeeName,
Department,
Salary,
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS DepartmentRank
FROM Employees;📊 Example Output
| Employee | Department | Salary | DepartmentRank |
|---|---|---|---|
| Alice | Sales | 60000 | 1 |
| Bob | Sales | 55000 | 2 |
| Charlie | IT | 75000 | 1 |
| David | IT | 70000 | 2 |
| Emma | HR | 50000 | 1 |
💡 Example: Return the Highest-Paid Employee in Each Department
Top Employee per Department
SELECT *
FROM (
SELECT
EmployeeName,
Department,
Salary,
ROW_NUMBER() OVER (
PARTITION BY Department
ORDER BY Salary DESC
) AS RowNum
FROM Employees
) RankedEmployees
WHERE RowNum = 1;This query returns only the highest-paid employee from each department.
💡 Example: Pagination
ROW_NUMBER() is commonly used to retrieve a specific range of rows for paginated results.
Pagination Example
SELECT *
FROM (
SELECT
EmployeeID,
EmployeeName,
ROW_NUMBER() OVER (
ORDER BY EmployeeID
) AS RowNum
FROM Employees
) EmployeeList
WHERE RowNum BETWEEN 11 AND 20;⚖️ ROW_NUMBER() vs RANK() vs DENSE_RANK()
| Function | Duplicate Values | Gaps in Ranking |
|---|---|---|
| ROW_NUMBER() | Always assigns unique numbers. | Not applicable. |
| RANK() | Same rank for ties. | ✅ Yes |
| DENSE_RANK() | Same rank for ties. | ❌ No |
Important
If multiple rows have identical ordering values, ROW_NUMBER()still assigns different numbers. If you need tied rows to share the same rank, use RANK() or DENSE_RANK().
💼 Real-World Applications
- 🏆 Rank employees within each department.
- 📄 Implement paginated search results.
- 📊 Select the latest record for each customer.
- 🧹 Remove duplicate records while keeping one row.
- 🛒 Retrieve the highest-selling product in each category.
🗄️ Database Compatibility
| Database System | ROW_NUMBER() Support |
|---|---|
| MySQL | ✅ Supported in MySQL 8.0 and later. |
| PostgreSQL | ✅ Fully supported. |
| SQL Server | ✅ Fully supported. |
| Oracle | ✅ Fully supported. |
| SQLite | ✅ Supported in SQLite 3.25.0 and later. |
⚠️ Common Mistakes
- ❌ Omitting the ORDER BY clause inside OVER().
- ❌ Assuming row numbers remain the same if the ordering changes.
- ❌ Using ROW_NUMBER() when tied rows should share the same rank.
- ❌ Forgetting to use PARTITION BY when numbering should restart for each group.
Warning
When the ORDER BY column contains duplicate values, ROW_NUMBER() may assign different row numbers to tied rows. To ensure consistent results, include additional columns in the ORDER BY clause to create a deterministic ordering.
⚠️ Best Practices
Best Practice
Always specify a meaningful ORDER BY clause, use PARTITION BY when numbering should restart within groups, consider RANK() or DENSE_RANK() for tied rankings, and use descriptive aliases for generated row numbers to improve query readability.
🚀 Key Points to Remember
- 📌 ROW_NUMBER() assigns a unique sequential number to each row.
- 📌 It is a window function and requires the OVER() clause.
- 📌 ORDER BY determines the numbering sequence.
- 📌 PARTITION BY restarts numbering for each group.
- 📌 It is ideal for pagination, deduplication, and top-N queries.
- 📌 Unlike RANK(), duplicate values never share the same row number.
>>"ROW_NUMBER() gives every row a unique place in line, even when values are identical."
Summary
✅ ROW_NUMBER() is a powerful SQL window function that assigns a unique sequential number to every row based on a specified ordering. It is widely used for pagination, ranking, selecting top records within groups, and removing duplicates while preserving complete row-level detail.