How To Get row_number in MySQL

Last updated on June 12th, 2024 at 10:15 am

Sometimes you may need to get row number in MySQL for reporting and analysis. Row number is very useful in ranking and sorting data. It is also helpful in assigning row numbers to each row, filtering data based on row number value, removing duplicates, pagination of query results and more. In this article, we will look at how to get row_number in MySQL.

How To Get row_number in MySQL

MySQL 8+ features row_number() function which is useful in retrieving row numbers for query results. For MySQL older than 8.0, you need to use session variables to assign row numbers. In this article, we will look at both these solutions.

1. Using ROW_NUMBER()

Row_number() function is available out of the box since MySQL 8.0. It is a window function, that allows you to perform some operations across all rows on a table, in relation to the current row. You can use it to assign row number starting 1 for the first row to the last row in the table. You can also partition your table into groups of rows and use row_number() function to separately number rows of each partition.

Here is the syntax of row_number() syntax. Please note, the PARTITION BY clause is optional.

ROW_NUMBER() OVER (
     PARTITION BY <expression(s)> 
     ORDER BY <expression(s)> [ASC|DESC])

Let us say you have the following sales table.

 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    1 | 2021-01-01 |    200 |
 |    2 | 2021-01-02 |    250 |
 |    3 | 2021-01-03 |    220 |
 |    4 | 2021-01-04 |    230 |
 |    5 | 2021-01-05 |    210 |
 |    6 | 2021-01-06 |    100 |
 |    7 | 2021-01-07 |    120 |
 |    8 | 2021-01-08 |    150 |
 |    9 | 2021-01-09 |    180 |
 |   10 | 2021-01-10 |    200 |
 +------+------------+--------+

Here is an example of using row_number function to rank rows in descending order of amount column.

mysql> select row_number() over (
       order by amount desc) row_num,
       amount
       from sales
       order by amount desc;
 +---------+--------+
 | row_num | amount |
 +---------+--------+
 |       1 |    250 |
 |       2 |    230 |
 |       3 |    220 |
 |       4 |    210 |
 |       5 |    200 |
 |       6 |    200 |
 |       7 |    180 |
 |       8 |    150 |
 |       9 |    120 |
 |      10 |    100 |
 +---------+--------+

In the above query, we treat the entire table as a single partition and don’t provide PARTITION BY clause. We also order these rows in descending order by amount column and use row_number() function to rank these rows.

2. Using Variable

However, if you are using MySQL <8.0 then here are the steps to get row_number in MySQL.

mysql> SELECT t.*, @rownum := @rownum + 1 AS rank 
          FROM sales t, (SELECT @rownum := 0) r  
          order by amount desc;
 +------+---------------------+--------+------+
 | id   | order_date          | amount | rank |
 +------+---------------------+--------+------+
 |    1 | 2021-02-02 08:15:00 |    250 |    1 |
 |   10 | 2021-02-02 11:15:00 |    250 |    2 |
 |    5 | 2021-02-02 09:30:00 |    250 |    3 |
 |    9 | 2021-02-02 10:45:00 |    200 |    4 |
 |   12 | 2021-02-02 11:45:00 |    200 |    5 |
 |    6 | 2021-02-02 09:45:00 |    200 |    6 |
 |    2 | 2021-02-02 08:30:00 |    200 |    7 |
 |    7 | 2021-02-02 10:15:00 |    180 |    8 |
 |    3 | 2021-02-02 08:55:00 |    150 |    9 |
 |   11 | 2021-02-02 11:30:00 |    150 |   10 |
 |    4 | 2021-02-02 09:15:00 |    125 |   11 |
 |    8 | 2021-02-02 10:30:00 |    125 |   12 |
 +------+---------------------+--------+------+

In the above SQL query, we use a temporary variable rownum to store row number. rownum is set to 0. When MySQL sequentially traverses the rows, it assigns rownum to each row in an incremental manner.

Conclusion

Very often database programmers and analysts need to use row numbers. By default, if you have an auto increment primary key without any gaps in its values, then you can directly use it to fetch data as per your requirement. But as your table undergoes insertion and deletions, the primary key will no longer be continuous. Therefore, it is better to separately generate row numbers for each row. If you are using MySQL < 8.0, then you can use a session variable to save row numbers. MySQL 8+ provides row_number() that allows you to number all rows in a table as a single group or multiple partitions. Numbering of rows is required to pick top N rows from a table, filter N rows in each group, remove duplicates and paginate query results. You can customize them as per your requirement.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Get Data for Every Hour in MySQL
How to Get Last 1 hour data in MySQL