MySQL ROW_NUMBER Function
Summary: in this tutorial, you will learn about the MySQL ROW_NUMBER() function and how to use it to generate a sequential number for each row in the result set.
MySQL ROW_NUMBER() syntax
MySQL introduced the ROW_NUMBER() function since version 8.0. The ROW_NUMBER() is a window function or analytic function that assigns a sequential number to each row in the result set. The first number begins with one.
Notice that if you use MySQL with a version less than 8.0, you can emulate some functionality of the ROW_NUMBER() function using various techniques.
The following shows the syntax of the ROW_NUMBER()function:
ROW_NUMBER() OVER (<partition_definition> <order_definition>)Code language: SQL (Structured Query Language) (sql)
partition_definition
The partition_definition has the following syntax:
PARTITION BY <expression>,[{,<expression>}...]Code language: SQL (Structured Query Language) (sql)
The PARTITION BY clause breaks the rows into smaller sets. The expression can be any valid expression that would be used in the GROUP BY clause. It’s possible to use multiple expressions separated by a comma (,).
The PARTITION BY clause is optional. If you omit it, the entire result set is considered a partition. However, when you use the PARTITION BY clause, each partition can be also considered as a window.
order_definition
The order_definition syntax looks like the following:
ORDER BY <expression> [ASC|DESC],[{,<expression>}...]Code language: SQL (Structured Query Language) (sql)
The purpose of the ORDER BY clause is to set the orders of rows. This ORDER BY clause is independent of the ORDER BY clause of the query.
MySQL ROW_NUMBER() function examples
Let’s use the products table from the sample database for the demonstration:
1) Assigning sequential numbers to rows
The following statement uses the ROW_NUMBER() function to assign a sequential number to each row from the products table:
SELECT
ROW_NUMBER() OVER (
ORDER BY productName
) row_num,
productName,
msrp
FROM
products
ORDER BY
productName;Code language: SQL (Structured Query Language) (sql)
Here is the output:
2) Finding top N rows of every group
You can use the ROW_NUMBER() function to find the top N rows for every group, for example, top three sales employees by sales channels or top five high-performance products by product categories.
The following statement uses the ROW_NUMBER() to find the top three products by product line that have the highest inventory:
WITH inventory
AS (SELECT
productLine,
productName,
quantityInStock,
ROW_NUMBER() OVER (
PARTITION BY productLine
ORDER BY quantityInStock DESC) row_num
FROM
products
)
SELECT
productLine,
productName,
quantityInStock
FROM
inventory
WHERE
row_num <= 3;Code language: SQL (Structured Query Language) (sql)
In this example,
- First, we used the
ROW_NUMER()function to rank the inventory of all products in each product line by partitioning all products by product line and ordering them by quantity in stock in descending order. As the result, each product is assigned a rank based on its quantity in stock. and the rank is reset for each product line. - Then, we selected only products whose rank is less than or equal to three.
The following shows the output:
3) Removing duplicate rows
You can use the ROW_NUMBER() to turn non-unique rows into unique rows and then delete the duplicate rows. Consider the following example.
First, create a table with some duplicate values:
CREATE TABLE t (
id INT,
name VARCHAR(10) NOT NULL
);VALUES(1,‘A’),
(2,‘B’),
(2,‘B’),
(3,‘C’),
(3,‘C’),
(3,‘C’),
(4,‘D’);
Code language: SQL (Structured Query Language) (sql)
Second, use the ROW_NUMBER() function to divide the rows into partitions by all columns. The row number will restart for each unique set of rows.
SELECT
id,
name,
ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS row_num
FROM t;Code language: SQL (Structured Query Language) (sql)
As you can see from the output, the unique rows are the ones with the row number 1.
Third, you can use the common table expression (CTE) to return the duplicate rows and delete statement to remove:
WITH dups AS (SELECT
id,
name,
ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id) AS row_num
FROM t)WHERE dups.row_num <> 1;
Code language: SQL (Structured Query Language) (sql)
Notice that MySQL does not support CTE-based delete. Therefore, you have to join the original table with the CTE as a workaround.
4) Pagination using ROW_NUMBER() function
Because the ROW_NUMBER() assigns each row in the result set a unique number, you can use it for pagination.
Suppose, you need to display a list of products with 10 products per page. To get the products for the second page, you use the following query:
SELECT *
FROM
(SELECT productName,
msrp,
row_number()
OVER (order by msrp) AS row_num
FROM products) t
WHERE row_num BETWEEN 11 AND 20; Code language: SQL (Structured Query Language) (sql)
Here’s the output:
In this tutorial, you have learned how to use the MySQL ROW_NUMBER() function to generate a sequential number for each row in a result set.