ROW_NUMBER()
Learn how to use the ROW_NUMBER() Window Function in SQL Server to assign a unique sequential number to each row.
What is ROW_NUMBER()?
ROW_NUMBER() is a SQL Server Window Function that assigns a unique sequential number to each row returned by a query.
The numbering starts from 1 and continues according to the order specified inside the OVER() clause.
It is commonly used for ranking, pagination, duplicate detection and selecting specific rows.
Syntax
ROW_NUMBER()
OVER
(
ORDER BY column_name
)
The ORDER BY clause determines the order in which the row numbers are assigned.
Basic Example
Suppose we have an Employees table containing employee details.
SELECT
EmployeeId,
EmployeeName,
Salary,
ROW_NUMBER() OVER
(
ORDER BY Salary DESC
) AS RowNumber
FROM Employees;
Sample Employee Data
| EmployeeId | EmployeeName | Salary |
|---|---|---|
| 101 | Amit | 70000 |
| 102 | Neha | 60000 |
| 103 | Rahul | 80000 |
| 104 | Priya | 65000 |
Output
Since the query uses ORDER BY Salary DESC, the highest salary receives row number 1.
| RowNumber | EmployeeId | EmployeeName | Salary |
|---|---|---|---|
| 1 | 103 | Rahul | 80000 |
| 2 | 101 | Amit | 70000 |
| 3 | 104 | Priya | 65000 |
| 4 | 102 | Neha | 60000 |
ROW_NUMBER() with PARTITION BY
PARTITION BY divides the result into groups. ROW_NUMBER() then starts numbering from 1 separately for each group.
SELECT
EmployeeId,
EmployeeName,
Department,
Salary,
ROW_NUMBER() OVER
(
PARTITION BY Department
ORDER BY Salary DESC
) AS RowNumber
FROM Employees;
Practical Example
Suppose employees belong to different departments. We want to find the highest-paid employee in each department.
WITH EmployeeRanking AS
(
SELECT
EmployeeId,
EmployeeName,
Department,
Salary,
ROW_NUMBER() OVER
(
PARTITION BY Department
ORDER BY Salary DESC
) AS RowNumber
FROM Employees
)
SELECT
EmployeeId,
EmployeeName,
Department,
Salary
FROM EmployeeRanking
WHERE RowNumber = 1;
This query returns the employee with the highest salary from each department.
Uses of ROW_NUMBER()
- Assigning sequential numbers to records.
- Finding the first record from each group.
- Finding the latest record from each group.
- Removing duplicate records.
- Implementing pagination.
- Finding top records from each category.
Important Points
- ROW_NUMBER() starts from 1.
- ROW_NUMBER() requires the OVER() clause.
- ORDER BY determines the numbering order.
- PARTITION BY restarts numbering for every group.
- Each row receives a unique row number within its partition.
Summary
ROW_NUMBER() assigns a unique sequential number to every row according to the specified ordering. When combined with PARTITION BY, the numbering restarts for each group.