SQL Server ROW_NUMBER Function

Summary: in this tutorial, you will learn how to use the SQL Server ROW_NUMBER() function to assign a sequential integer to each row of a result set.

Introduction to SQL Server ROW_NUMBER() function

The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.

The following shows the syntax of the ROW_NUMBER() function:

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

Let’s examine the syntax of the ROW_NUMBER() function in detail.

PARTITION BY

The PARTITION BY clause divides the result set into partitions (another term for groups of rows). The ROW_NUMBER() function is applied to each partition separately and reinitialized the row number for each partition.

The PARTITION BY clause is optional. If you skip it, the ROW_NUMBER() function will treat the whole result set as a single partition.

ORDER BY

The ORDER BY clause defines the logical order of the rows within each partition of the result set. The ORDER BY clause is mandatory because the ROW_NUMBER() function is order-sensitive.

SQL Server ROW_NUMBER() function examples

We’ll use the sales.customers table from the sample database to demonstrate the ROW_NUMBER() function.

Using SQL Server ROW_NUMBER() function over a result set example

The following statement uses the ROW_NUMBER() to assign each customer row a sequential number:

SELECT 
   ROW_NUMBER() OVER (
	ORDER BY first_name
   ) row_num,
   first_name, 
   last_name, 
   city
FROM 
   sales.customers;Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In this example, we skipped the PARTITION BY clause, therefore, the ROW_NUMBER() treated the whole result set as a single partition.

Using SQL Server ROW_NUMBER() over partitions example

The following example uses the ROW_NUMBER() function to assign a sequential integer to each customer. It resets the number when the city changes:

SELECT 
   first_name, 
   last_name, 
   city,
   ROW_NUMBER() OVER (
      PARTITION BY city
      ORDER BY first_name
   ) row_num
FROM 
   sales.customers
ORDER BY 
   city;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this example, we used the PARTITION BY clause to divide the customers into partitions by city. The row number was reinitialized when the city changed.

Using SQL Server ROW_NUMBER() for pagination

The ROW_NUMBER() function is useful for pagination in applications. For example, you can display a list of customers by page, where each page has 10 rows.

The following example uses the ROW_NUMBER() to return customers from rows 11 to 20, which is the second page:

WITH cte_customers AS (
    SELECT 
        ROW_NUMBER() OVER(
             ORDER BY 
                first_name, 
                last_name
        ) row_num, 
        customer_id, 
        first_name, 
        last_name
     FROM 
        sales.customers
) SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    cte_customers
WHERE 
    row_num > 10 AND 
    row_num <= 20;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

In this example:

• First, the CTE used the ROW_NUMBER() function to assign every row in the result set to a sequential integer.
• Second, the outer query returned the rows of the second page, which have row numbers between 11 and 20.

Summary

• Use the ROW_NUMBER() function to assign a sequential integer to each row within a partition of a query.