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. Each row in the result set is assigned a number starting from 1 for the first row in each partition.
Here’s a general syntax for using the ROW_NUMBER() function:
SELECT
ROW_NUMBER() OVER (PARTITION BY [column_name] ORDER BY [column_name]) AS row_num,
[other_columns]
FROM
[table_name];
Explanation of Components:
1. PARTITION BY: This clause divides the result set into partitions to which the ROW_NUMBER() function is applied. If you want a unique number for all rows with no partitioning, you can omit the PARTITION BY clause.
2. ORDER BY: This clause determines the order in which the row numbers are assigned. It is essential to specify this in order to ensure that the row numbers are assigned consistently.
Example:
Let’s say you have a table Employees with the following columns: EmployeeID, FirstName, and Department. If you want to assign a unique row number to each employee within their department, your SQL query might look like this:
SELECT
EmployeeID,
FirstName,
Department,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY EmployeeID) AS RowNumber
FROM
Employees;
Explanation of the Example:
- The ROW_NUMBER() function will assign a number starting from 1 to each employee in the same department.
- The ORDER BY EmployeeID will ensure that employees are numbered based on their EmployeeID within each department.
Without Partitioning:
If you'd like to assign a unique row number across the entire table without partitioning, it would look like this:
SELECT
EmployeeID,
FirstName,
ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNumber
FROM
Employees;
In this case, all rows from the Employees table will receive a unique row number regardless of the department.