The ROW_NUMBER() function is a window function in SQL that assigns a unique sequential integer to rows within a partition of a result set. When used with the PARTITION BY clause, it allows you to reset the row numbering for each partition. Here’s how you can use it:
Syntax
ROW_NUMBER() OVER (PARTITION BY column_1, column_2 ORDER BY column_3)
- PARTITION BY is used to define the partitions to which the row numbering should be applied.
- ORDER BY is used to specify the order of rows within each partition.
Example
Let's say you have a table called Employees with the following columns: DepartmentId, EmployeeId, and EmployeeName. You want to assign a sequential number to each employee within their respective departments.
SELECT
DepartmentId,
EmployeeId,
EmployeeName,
ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY EmployeeId) AS RowNum
FROM
Employees;
Explanation of the Example
- PARTITION BY DepartmentId: This means that the row numbering will restart for each department. Each department's employees will have their own sequential numbering starting from 1.
- ORDER BY EmployeeId: This specifies that the employees should be ordered by their EmployeeId within each department when assigning the row number.
Result
If your Employees table looks like this:
| DepartmentId | EmployeeId | EmployeeName |
|--------------|------------|---------------|
| 1 | 101 | Alice |
| 1 | 102 | Bob |
| 2 | 201 | Charlie |
| 2 | 202 | David |
| 1 | 103 | Eve |
The output of the above query would be:
| DepartmentId | EmployeeId | EmployeeName | RowNum |
|--------------|------------|---------------|--------|
| 1 | 101 | Alice | 1 |
| 1 | 102 | Bob | 2 |
| 1 | 103 | Eve | 3 |
| 2 | 201 | Charlie | 1 |
| 2 | 202 | David | 2 |
In this result set, each department has its own row numbering that starts from 1.