MySQL Row Number Function & Its Uses

While using MySQL, many users need to get row number of a record or perform some operation based on the row number. Till MySQL 8.0 was released, this used to be very tedious as you needed to sort the table and assign row number to temporary variable. Since version 8.0, MySQL supports ROW_NUMBER() function which directly returns the row number for you. In this article, we will look at MySQL Row number function and its applications.

MySQL Row Number Function & Its Uses

MySQL row_number() is a window function that assigns a sequential row number to each row in the result set. The row number starts with 1. You can partition your table or query result into different groups and assign row numbers to each row in a groupwise manner. In this case, first row number for each group will start with 1.

Here is the syntax of row_number() function.

ROW_NUMBER() OVER (
   PARTITION BY <expression>,[{,<expression>}...]
   ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
)

In the above statement, PARTITION BY partitions the table or result set into smaller groups. The expression for PARTITION BY would be one or more expressions used in GROUP BY clause of SQL query. Please note, PARTITION BY clause is optional.

ORDER BY clause sorts rows in your table, or query result. If you have used PARTITION BY clause, then MySQL will order rows within each PARTITION separately. Please note, this ORDER BY works independently from the ORDER BY clause of your SQL query.

Now let us look at some of the most common applications of row_number() function.

Let us say you have the following table.

mysql> create table prod_list(product varchar(10),amount int);

mysql> insert into prod_list(product, amount) values('a',10),('c',5),('b',20),('d',15);

mysql> select * from prod_list;
+---------+--------+
| product | amount |
+---------+--------+
| a       |     10 |
| c       |     20 |
| b       |      5 |
| d       |     15 |
+---------+--------+

1. Assign Row Numbers

Here is the query to assign sequential row numbers to our table.

SELECT 	
    product,
    amount,
ROW_NUMBER() OVER (
		ORDER BY product
	) row_num
FROM 
	prod_list
ORDER BY 
	product;

Here is the output

+---------+--------+---------+
| product | amount | row_num |
+---------+--------+---------+
| a       |     10 |   1     |
| b       |      5 |   2     |
| c       |     20 |   3     |
| d       |     15 |   4     |
+---------+--------+---------+

2. Find top N rows

It is a common requirement by database developers and web developers to get the first N rows in a table or group of rows. You can query the result of above query to get the top N rows easily. Here is an example to get the top 2 rows from our table.

mysql> select * from (
    SELECT 	
    product,
    amount,
    ROW_NUMBER() OVER (
		ORDER BY product
	) row_num
    FROM 
	prod_list
    ORDER BY 
	product
) tmp where row_num<=2;

+---------+--------+---------+
| product | amount | row_num |
+---------+--------+---------+
| a       |     10 |   1     |
| b       |      5 |   2     |
+---------+--------+---------+

If you have multiple expressions in PARTITION BY then MySQL will get top N rows for each partition. Here’s an example to get top 2 rows for each sales rep.


mysql> select * from (
    SELECT 	
    product,
    amount,
    ROW_NUMBER() OVER (
                PARTITION BY sales_rep
		ORDER BY product
	) row_num
    FROM 
	prod_list
    ORDER BY 
	product
) tmp where row_num<=2;

3. For Pagination

You can also use the above query for pagination. Suppose you have a large list of products and you want to display products for the 3rd page, that is product with row numbers 21-30, then here is the SQL query for it.


mysql> select * from (
    SELECT 	
    product,
    amount,
    ROW_NUMBER() OVER (
		ORDER BY product
	) row_num
    FROM 
	prod_list
    ORDER BY 
	product
) tmp where row_num>=21 and row_num<30;

In this article, we have learnt how to use row_number() function and some of its common applications.

MySQL Row Number Function & Its Uses