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: