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:

ROW_NUMBER() OVER (<partition_definition> <order_definition>)  

Let us demonstrate it using an example.

First, we are going to create a table named “Person” using the below statement:

 CREATE TABLE Person (  

  Name varchar(45) NOT NULL,  

  Product varchar(45) DEFAULT NULL,  

  Country varchar(25) DEFAULT NULL,  

  Year int NOT NULL  

); 

Next, it is required to add values to this table. Execute the below statement:

 INSERT INTO Person(Name, Product, Country, Year)   

VALUES ('Stephen', 'Computer', 'USA', 2015),   

('Joseph', 'Laptop', 'India', 2016),   

('John', 'TV', 'USA', 2016),  

('Donald', 'Laptop', 'England', 2015),  

('Joseph', 'Mobile', 'India', 2015),  

('Peter', 'Mouse', 'England', 2016); 

Next, execute the SELECT statement to display the records:

mysql> SELECT * FROM Person;  

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:

SELECT *,   

    ROW_NUMBER() OVER(PARTITION BY Year) AS row_num  

FROM Person; 

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:

SELECT *,   

    ROW_NUMBER() OVER(PARTITION BY Year) AS row_num  

FROM Person;

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:

SET @row_number = 0;   

  

SELECT Name, Product, Year, Country,  

    (@row_number:=@row_number + 1) AS row_num  

FROM Person ORDER BY Country;

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:

SELECT (@row_number:=@row_number + 1) AS row_num, Name, Country, Year  

FROM Person, (SELECT @row_number:=0) AS temp ORDER BY Year;

We will get the output as below: