SQLite ROW_NUMBER

Summary : in this tutorial, you will learn how to use the SQLite ROW_NUMBER() 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 row number. The row number is reset for each partition.

SQLite ROW_NUMBER() examples

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

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

Here is the partial output:

Using SQLite 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 
    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 customers by into partitions by country.
• Second, the ORDER BY clause sorts customers 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.

Using SQLite ROW_NUMBER() for pagination

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

The following statement returns customers information from row 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 
        customers
WHERE 
    RowNum > 20 AND RowNum <= 30
Code 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.

Using SQL 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 
SELECT
    CustomerId,
    FirstName,
    LastName,
    Country,
    SUM( total ) Amount 
    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
	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