SQLite ROW_NUMBER() Function

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

Introduction to SQLite ROW_NUMBER() function

The ROW_NUMBER() is a window function that assigns a sequential integer to each row of a query’s result set. Rows are ordered starting from one based on the order specified by the ORDER BY clause in the window definition.

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

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

In this syntax,

• First, the PARTITION BY clause divides the rows derived from the FROM clause into partitions. The PARTITION BY clause is optional. If you skip it, the ROW_NUMBER() will treat the whole result set as a single partition.
• Then, the ORDER BY clause specifies the order of the rows in each partition. The ORDER BY clause is mandatory because the ROW_NUMBER() function is order sensitive.
• Finally, each row in each partition is assigned a sequential integer number called a row number. The row number is reset for each partition.

SQLite ROW_NUMBER() function examples

We will use the customers and invoices tables from the sample database for the demonstration.

1) Using SQLite ROW_NUMBER() with ORDER BY clause example

The following statement returns the first name, last name, and country of all customers. In addition, it uses the ROW_NUMBER() function to add a sequential integer to each customer record.

SELECT
    ROW_NUMBER () OVER ( 
        ORDER BY Country 
    ) RowNum,
    FirstName,
    LastName,
    country 
FROM
    customers;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

2) Using the ROW_NUMBER() with PARTITION BY example

The following statement assigns a sequential integer to each customer and resets the number when the country of the customer changes:

SELECT
    ROW_NUMBER () OVER ( 
        PARTITION BY Country
        ORDER BY FirstName
    ) RowNum,
    FirstName,
    LastName,
    country 
FROM
    customers;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this example:

• First, the PARTITION BY clause divides the rows in the customers table into partitions by country.
• Second, the ORDER BY clause sorts rows in each partition by the first name.
• Third, the ROW_NUMBER() function assigns each row in each partition a sequential integer and resets the number when the country changes.

3) Using the ROW_NUMBER() function for pagination

The ROW_NUMBER() function can be useful for pagination. For example, if you want to display customer information on a table by pages with 10 rows per page.

The following statement returns customer data from rows 21 to 30, which is the third page with 10 rows per page:

SELECT
  *
FROM
  (
    SELECT
      ROW_NUMBER() OVER (
        ORDER BY
          FirstName
      ) RowNum,
      FirstName,
      LastName,
      Country
    FROM
      customers
  ) t
WHERE
  RowNum > 20
  AND RowNum <= 30Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example:

• First, the ROW_NUMBER() function assigns each row a sequential integer.
• Second, the outer query selects the row from 20 to 30.

4) Using the ROW_NUMBER() to find the nth highest value per group

The following statement creates a new view named Sales that consists of customer id, first name, last name, country, and amount. The amount is retrieved from the invoices table:

CREATE VIEW Sales 
AS
SELECT
    CustomerId,
    FirstName,
    LastName,
    Country,
    SUM( total ) Amount 
FROM
    invoices 
    INNER JOIN customers USING (CustomerId)
GROUP BY
    CustomerId;
Code language: SQL (Structured Query Language) (sql)

The following query returns the data from the Sales view:

SELECT * FROM sales;Code language: SQL (Structured Query Language) (sql)

The following statement finds the customers who have the highest amounts in each country:

SELECT 
    Country,
    FirstName,
    LastName,
    Amount
FROM (
    SELECT 
        Country, 
        FirstName,
        LastName,
        Amount,
        ROW_NUMBER() OVER (
            PARTITION BY country 
            ORDER BY Amount DESC
        ) RowNum
    FROM 
        Sales )
WHERE
    RowNum = 1;Code language: SQL (Structured Query Language) (sql)

The output is as follows:

In the subquery:

• First, the PARTITION BY clause divides the customers by country.
• Second, the ORDER BY clause sorts the customers in each country by the amount from high to low.
• Third, the ROW_NUMBER() assigns each row a sequential integer. It resets the number when the country changes.

The outer query selects the customers that have the RowNum with the value 1.

If you change the row number in the WHERE clause to 2, 3, and so on, you will get the customers who have the second-highest amount, the third-highest amount, etc.

Summary

• Use the ROW_NUMBER() function to assign a sequential integer to each row in the query’s result set.