Summary: in this tutorial, you’ll learn how to use the ROW_NUMBER()
to assign a sequential number to each row in a query result set.
Introduction to SQL ROW_NUMBER() Function #
The ROW_NUMBER()
is a window function that assigns a sequential integer number to each row in the result set.
Here’s the syntax of the ROW_NUMBER()
function:
ROW_NUMBER() OVER (
[PARTITION BY expr1, expr2,...]
ORDER BY expr1 [ASC | DESC], expr2,...
)
Code language: SQL (Structured Query Language) (sql)
In this syntax,
- The
PARTITION BY
clause divides the result set into partitions. ThePARTITION BY
clause is optional. If you omit it, theROW_NUMBER
function treats the whole result set as a single partition. - The
ORDER BY
clause sorts the rows in each partition. Since theROW_NUMBER()
is an order sensitive function, theORDER BY
clause is mandatory. - The
ROW_NUMBER()
function assigns a sequential integer number to each row in within each partition. It resets the row number whenever the partition boundary is crossed.
SQL ROW_NUMBER() function examples #
We’ll use the employees
and departments
tables from the sample database for the demonstration:
Basic ROW_NUMBER() function example #
The following statement retrieves the first name, last name, and salary of all employees and uses the ROW_NUMBER()
function to add sequential integer number to each row.
SELECT
ROW_NUMBER() OVER (
ORDER BY
salary
) row_num,
first_name,
last_name,
salary
FROM
employees;
Code language: SQL (Structured Query Language) (sql)
The following picture shows the partial result set:
Using SQL ROW_NUMBER() function for pagination #
The ROW_NUMBER()
function can be useful for for pagination.
For example, if you want to display all employees on a table in an application by pages, which each page has ten records.
- First, use the
ROW_NUMBER()
function to assign each row a sequential integer number. - Second, filter rows by the requested page. The first page includes the rows starting from 1 to 10, and the second page include rows starting from 11 to 20, and so on.
The following statement returns the rows of the second page:
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (
ORDER BY
salary
) row_num,
first_name,
last_name,
salary
FROM
employees
) t
WHERE
row_num > 10
AND row_num <= 20;
Code language: SQL (Structured Query Language) (sql)
The following picture shows the output:
You can use a common table expression (CTE) instead of the subquery:
WITH
t AS (
SELECT
ROW_NUMBER() OVER (
ORDER BY
salary
) row_num,
first_name,
last_name,
salary
FROM
employees
)
SELECT
*
FROM
t
WHERE
row_num > 10
AND row_num <= 20;
Code language: SQL (Structured Query Language) (sql)
Finding the nth highest value per group #
The following example shows you how to find the employees who have the highest salary in their departments:
SELECT
department_name,
first_name,
last_name,
salary
FROM
(
SELECT
department_name,
ROW_NUMBER() OVER (
PARTITION BY department_name
ORDER BY salary DESC) row_num,
first_name,
last_name,
salary
FROM
employees e
INNER JOIN departments d
ON d.department_id = e.department_id
) t
WHERE
row_num = 1;
Code language: SQL (Structured Query Language) (sql)
In the subquery:
- First, the
PARTITION BY
clause distributes the employees by departments. - Second, the
ORDER BY
clause sorts the employee in each department by salary in the descending order. - Third, the
ROW_NUMBER()
assigns each row a sequential integer number. It resets the number when the department changes.
The following shows the result set of the subquery:
In the outer query, we select only the employee rows which have the row_num
with the value 1.
Here is the output of the whole query:
If you change the predicate in the WHERE
clause from 1 to 2, 3, and so on, you will get the employees who have the second highest salary, third highest salary, and so on.
Summary #
- Use the SQL
ROW_NUMBER()
function to assign a sequential integer number to each row within the same partition.
Databases #
- PostgreSQL ROW_NUMBER function
- Oracle ROW_NUMBER function
- SQL Server ROW_NUMBER function
- MySQL ROW_NUMBER function
- SQLite ROW_NUMBER function