MySQL ROW_NUMBER() Function
The ROW_NUMBER() function in MySQL is used to returns the sequential number for each row within its partition. It is a kind of window function. The row number starts from 1 to the number of rows present in the partition.
It is to be noted that MySQL does not support the ROW_NUMBER() function before version 8.0, but they provide a session variable that allows us to emulate this function.
Syntax
The following are the basic syntax to use ROW_NUMBER() in MySQL:
Let us demonstrate it using an example.
First, we are going to create a table named “Person” using the below statement:
Next, it is required to add values to this table. Execute the below statement:
Next, execute the SELECT statement to display the records:
We will get the output, as shown below:
Now, we can use the ROW_NUMBER() function to assign a sequence number for each record using the below statement:
It will give the following output:
Again, we can use the ROW_NUMBER() function to assign a sequence number for each record within a partition using the below statement:
It will give the output as below where two partitions found based on the year (2015 and 2016).
MySQL ROW_NUMBER() Using Session Variable
We can emulate the ROW_NUMBER() function to add a row number in increasing order using the session variable.
Execute the below statement that add the row number for each row, which starts from 1:
In this statement, we have first specify the session variable @row_number indicated by @prfix and set its value 0. Then, we have selected the data from the table Person and increases the value for variable @row_number by one to each row.
After the successful execution of the queries, we will get the output as below:
Again, we are going to use a session variable as a table and cross join it with source table using the following statement:
We will get the output as below:
