SQL ROW_NUMBER

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:

ROW_NUMBER() OVER (
    [PARTITION BY expr1, expr2,...]
    ORDER BY expr1 [ASC | DESC], expr2,...
)
Code language: SQL (Structured Query Language) (sql)

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.

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:

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.

-- 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;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

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

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)

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:

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