ROW_NUMBER Function - SQL
Overview
The ROW_NUMBER function in SQL assigns a rank to each row within a partition of a result set. If there are ties in the values, it assigns the same rank to the tied rows and skips subsequent ranks.
Example:
SELECT department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees
Syntax:
SELECT column_name,
ROW_NUMBER() OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name
partition_column[s] is the column or columns that divide the result set into partitions.
order_column[s] is the column or columns that specify the order of rows within each partition.
ROW_NUMBER() assigns a unique sequential integer to each row within the partition, starting at 1 for the first row.
Sample Data:
Before
| department_id | first_name | salary |
|---|---|---|
| 3 | Frank | 123000 |
| 2 | Jane | 135000 |
| 3 | Ashley | 115000 |
| NULL | Glenn | 115000 |
| 2 | Kelly | 125000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 5 | Kyle | 200000 |
| 2 | James | 107000 |
| 1 | Gustavo | 100000 |
After
| department_id | first_name | salary |
|---|---|---|
| NULL | Glenn | 115000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 1 | Gustavo | 100000 |
| 2 | Jane | 135000 |
| 2 | Kelly | 125000 |
| 2 | James | 107000 |
| 3 | Ashley | 115000 |
| 3 | Frank | 123000 |
| 5 | Kyle | 200000 |
Example: Query without the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column, along with a ROW_NUMBER column that assigns a unique sequential number to each employee’s salary within their department, ordered by salary in descending order.
Example: Query with the WHERE Statement
In this example, we are filtering for only rows in department_id 1. We are returning the department_id, first_name, and salary column, along with a ROW_NUMBER column that assigns a unique sequential number to each employee’s salary within their department, ordered by salary in descending order.
