SQL ROW_NUMBER Function

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. The PARTITION BY clause is optional. If you omit it, the ROW_NUMBER function treats the whole result set as a single partition.
  • The ORDER BY clause sorts the rows in each partition. Since the ROW_NUMBER() is an order sensitive function, the ORDER 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:

Employees & Departments Tables

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)

Try it

The following picture shows the partial result set:

SQL ROW_NUMBER Function Example

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)

Try it

The following picture shows the output:

SQL ROW_NUMBER Function - Pagination Example

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)

Try it

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:

SQL ROW_NUMBER Function - 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:

SQL ROW_NUMBER Function - find nth value per group

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 #

Was this tutorial helpful ?