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 theFROM
clause into partitions. ThePARTITION BY
clause is optional. If you skip it, theROW_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. TheORDER BY
clause is mandatory because theROW_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 thecustomers
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 <= 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.
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.