MySQL ROW_NUMBER() Function

Syntax of MySQL row_number() Function

The syntax of row_number in MySQL is:

Here, <partition_definition> has the following syntax to partition the total rows into smaller subsets.

You can partition based on multiple expressions by mentioning them in a comma-separated format in the above syntax.

Similarly, the <order_definition> has the following syntax:

The <order_definition> will help in providing the ordering of rows in a given partition.

Parameters of MySQL row_number() Function

• partition_definition:

This is an optional parameter that partitions the rows into smaller subsets to which the row_number() function should assign row number.

• order_definition:

This is also an optional parameter that uses ORDER BY clause to define the order of the row data.

Return Value of MySQL row_number() Function

The return value of row_number in MySQL is the sequential order of the rows in a particular MySQL table, and the function return type is BIGINT.

How does the MySQL row_number() Function work?

The MySQL row_number() function works by assigning consecutive numbers to all partition rows. Assuming there are 5 rows, the row_number() function will assign these values - 1, 2, 3, 4, 5. The main function of row_number in MySQL is to assign a sequential ranking to each row. This is similar to MySQL's RANK() function, except that two rows with the same value do not have the same row number as in the case of the rank function in MySQL. The first row is always assigned a value of 1 and the number will be incremented in the subsequent rows.

Examples

Let's see few examples of using row_number in MySQL:

Let's first suppose we have a student_details table, having NAME, and DOB_YEAR as the fields, and some data has been stored in these fields.

To see the records in the table, we can make use of the SELECT statement:

1. Add a sequential row number to each row of a table based on partitions:

   Let's see how to use row_number in MySQL to assign a sequential number to each row in a partition.

   Query:

   Output:

   The output of the above query would be:

   NAME	DOB_YEAR	ROW_NUM
   Rita Goyal	2000	1
   Jyoti Sharma	2000	2
   Kanika Mittal	2001	1
   Apeksha Goyal	2001	2
   Reshma Kumari	2003	1
   Reeta Rupal	2004	1

   Explanation:

   Using the above query, we create a partition based on DOB_YEAR and each record in the partition is numbered from 1 to the total number of rows in that particular partition. Note that in this case this numbering within a partition is random, as there is no specific order for assigning row_number in MySQL within a particular partition.

2. Add a sequential row number to each row based on order:

   Now, let's use the ORDER BY clause with row_number in MySQL to sort the output data by the DOB_YEAR column.

   Query:

   Output:

   The output of the above query would be:

   NAME	DOB_YEAR	ROW_NUM
   Rita Goyal	2000	1
   Jyoti Sharma	2000	2
   Kanika Mittal	2001	3
   Apeksha Goyal	2001	4
   Reshma Kumari	2003	5
   Reeta Rupal	2004	6

   Explanation:

   Here, we used the ORDER BY clause to sort the output data by the DOB_YEAR column. You will get a unique row_number in MySQL because we are not using a PARTITION BY clause here.

3. Add a sequential row number to each row based on partitions and order:

   Now, let’s use the PARTITION By clause to partition the rows based on DOB_YEAR and ORDER BY clause to sort the output data by the NAME column.

   Query:

   Output:

   The output of the above query would be:

   NAME	DOB_YEAR	ROW_NUM
   Jyoti Sharma	2000	1
   Rita Goyal	2000	2
   Apeksha Goyal	2001	1
   Kanika Mittal	2001	2
   Reshma Kumari	2003	1
   Reeta Rupal	2004	1

   Explanation:

   Here, firstly the data was partitioned into row based on DOB_YEAR and then individual partitions were sorted by ORDER BY based on the NAME column.

4. Assigning row number using Session Variable:

   We can assign row numbers to a partition using the session variable. It is an alternative way of using row_number in MySQL.

   Query:

   Output:

   The output of the above query would be:

   NAME	DOB_YEAR	ROW_NUM
   Rita Goyal	2000	1
   Jyoti Sharma	2000	2
   Kanika Mittal	2001	3
   Apeksha Goyal	2001	4
   Reshma Kumari	2003	5
   Reeta Rupal	2004	6

   Explanation:

   Here, we have initialized the session variable to 0 and incremented it for every row in the table.