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 to which it applied beginning 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>)

partition_definition

The partition_definition has the following syntax:

PARTITION BY <expression>,[{,<expression>}...]

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. You can use multiple expressions separated by commas.

The PARTITION BY a 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 the syntax looks like the following:

ORDER BY <expression> [ASC|DESC],[{,<expression>}...]

The purpose of the ORDER BY a clause is to set the orders of rows. This ORDER BY a clause is independent of the ORDER BY the 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;

Here is the output:

2) Finding top N rows of every group

You can use the ROW_NUMBER() function for the queries that find the top N rows for every group, for example, the top three sales employees of every sales channel, top five high-performance products of every category.

The following statement finds the top three products that have the highest inventory of every product line:

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;

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
);

INSERT INTO t(id,name) 
VALUES(1,'A'),
      (2,'B'),
      (2,'B'),
      (3,'C'),
      (3,'C'),
      (3,'C'),
      (4,'D');

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;

As you can see from the output, the unique rows are the ones whose row number equals one.

Third, you can use the common table expression (CTE) to return the duplicate rows and delete statements to remove:

WITH dups AS (SELECT 
        id,
        name,
        ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id) AS row_num
    FROM t)

DELETE FROM t USING t JOIN dups ON t.id = dups.id
WHERE dups.row_num <> 1;

Notice that MySQL does not support CTE-based delete, therefore, we had 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;    

Here is 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.