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.

Introduction to MySQL ROW_NUMBER() function

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. Note that 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 the top N rows of every group

You can use the ROW_NUMBER() function to find the top N rows for every group, for example, the top three sales employees by sales channels or the 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 a 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 AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) NOT NULL
);

INSERT INTO t(name) 
VALUES('A'),
      ('B'),
      ('B'),
      ('C'),
      ('C'),
      ('C'),
      ('D');
SELECT * FROM t;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | B    |
|  4 | C    |
|  5 | C    |
|  6 | C    |
|  7 | D    |
+----+------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

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 name
    ORDER BY id
  ) AS row_num 
FROM 
  t;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------+---------+
| id | name | row_num |
+----+------+---------+
|  1 | A    |       1 |
|  2 | B    |       1 |
|  3 | B    |       2 |
|  4 | C    |       1 |
|  5 | C    |       2 |
|  6 | C    |       3 |
|  7 | D    |       1 |
+----+------+---------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that 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 the DELETE statement to remove them:

WITH dups AS (
  SELECT 
    id, 
    name, 
    ROW_NUMBER() OVER(
      PARTITION BY name 
      ORDER BY id
    ) AS row_num 
  FROM 
    t
) 
DELETE FROM 
  t USING t 
  INNER JOIN dups ON t.id = dups.id 
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.

Finally, select data from the t table to check for duplicates:

 select * from t;Code language: JavaScript (javascript)

Output:

+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  4 | C    |
|  7 | D    |
+----+------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

4) Pagination using the 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:

+------------------------------------------+-------+---------+
| productName                              | msrp  | row_num |
+------------------------------------------+-------+---------+
| 1936 Mercedes-Benz 500K Special Roadster | 53.91 |      11 |
| 1954 Greyhound Scenicruiser              | 54.11 |      12 |
| Pont Yacht                               | 54.60 |      13 |
| 1970 Dodge Coronet                       | 57.80 |      14 |
| 1962 City of Detroit Streetcar           | 58.58 |      15 |
| 1911 Ford Town Car                       | 60.54 |      16 |
| 1936 Harley Davidson El Knucklehead      | 60.57 |      17 |
| 1926 Ford Fire Engine                    | 60.77 |      18 |
| 1971 Alpine Renault 1600s                | 61.23 |      19 |
| 1950's Chicago Surface Lines Streetcar   | 62.14 |      20 |
+------------------------------------------+-------+---------+
10 rows in set (0.01 sec)

Summary

• Use the MySQL ROW_NUMBER() function to generate a sequential number for each row in a result set.