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 filtering data based on row number value. In this article, we will look at how to get row_number in MySQL.
How To Get row_number in MySQL
Row_number() function is available out of the box since MySQL 8.0.
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 | +------+------------+--------+
Also read : How to Get Data for Every Hour in MySQL
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.
Also read : How to Get Last 1 hour data in MySQL
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. When MySQL sequentially traverses the rows, it assigns rownum to each row in an incremental manner.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
