**Summary**: in this tutorial, you will learn how to use the `ROW_NUMBER()`

to assign a sequential number to each row in a query result set.

## SQL `ROW_NUMBER()`

Function Overview

The `ROW_NUMBER()`

is a window function that assigns a sequential integer number to each row in the query’s result set.

The following illustrates the syntax of the `ROW_NUMBER()`

function:

1 2 3 4 | ROW_NUMBER() OVER ( [PARTITION BY expr1, expr2,...] ORDER BY expr1 [ASC | DESC], expr2,... ) |

In this syntax,

- First, the
`PARTITION BY`

clause divides the result set returned from the`FROM`

clause into partitions. The`PARTITION BY`

clause is optional. If you omit it, the whole result set is treated as a single partition. - Then, the
`ORDER BY`

clause sorts the rows in each partition. Because the`ROW_NUMBER()`

is an order sensitive function, the`ORDER BY`

clause is required. - Finally, each row in each partition is assigned a sequential integer number called a row number. The row number is reset whenever the partition boundary is crossed.

## SQL `ROW_NUMBER()`

examples

We will use the `employees`

and `departments`

tables from the sample database for the demonstration:

### A) Simple SQL `ROW_NUMBER()`

example

The following statement finds the first name, last name, and salary of all employees. In addition, it uses the `ROW_NUMBER()`

function to add sequential integer number to each row.

1 2 3 4 5 6 7 8 9 | SELECT ROW_NUMBER() OVER ( ORDER BY salary ) row_num, first_name, last_name, salary FROM employees; |

The following picture shows the partial result set:

### B) Using SQL `ROW_NUMBER()`

for pagination

The `ROW_NUMBER()`

function can be used 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 requested page. For example, the first page has the rows starting from one to 9, and the second page has the rows starting from 11 to 20, and so on.

The following statement returns the records of the second page, each page has ten records.

1 2 3 4 5 6 7 8 9 10 11 12 13 | -- pagination get page #2 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; |

The following shows the output:

If you want to use the common table expression (CTE) instead of the subquery, here is the query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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; |

### C) Using SQL `ROW_NUMBER()`

for finding nth highest value per group

The following example shows you how to find the employees whose have the highest salary in their departments:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | -- find the highest salary per department 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; |

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 selected 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.

In this tutorial, you have learned how to use the SQL `ROW_NUMBER()`

function to assign a sequential integer number to each row in the result set of a query.