How to Use of Row_Number() Function in MySQL

1. Use of ROW_NUMBER() in MySQL Using ORDER BY Clause
2. Use of ROW_NUMBER() in MySQL Using PARTITION BY Clause
3. Use of ROW_NUMBER() in MySQL Using PARTITION BY and ORDER BY Clause
4. Replication of ROW_NUMBER() in MySQL Using Session Variable
5. Conclusion

In this tutorial, we will introduce how you can use the ROW_NUMBER() function in MySQL. It is a ranking method that assigns consecutive numbers within the partition starting from 1. It is important to note that two rows within the partition do not have the same number.

We will also see how PARTITION BY and ORDER BY affect the MySQL results. You have to use the ORDER BY clause for using ROW_NUMBER() as it is mandatory. But the PARTITION BY clause is optional.

The results will be indeterminate if you use both clauses, PARTITION BY and ORDER BY. Here, we’ll see how to emulate the ROW_NUMBER() function using the session variable to get the desired results.

Please note that ROW_NUMBER() was not available before MySQL Version 8.0. You see what is new in MySQL Version 8.0 here.

Use of ROW_NUMBER() in MySQL Using ORDER BY Clause

We will only use the ROW_NUMBER() function with the ORDER BY clause and observe the results. Let’s create the table first and populate some data into it.

Example Code:

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `test_db`.`tb_student` (
 STUDENT_ID	INTEGER NOT NULL,
 FIRST_NAME	VARCHAR(30) NOT NULL,
 LAST_NAME	VARCHAR(30) NOT NULL,
 GENDER	VARCHAR(30) NOT NULL,
 CITY_NAME	VARCHAR(64) NOT NULL,
 EMAIL_ADDRESS	VARCHAR(64) NOT NULL,
 REGISTRATION_YEAR INTEGER NOT NULL,
 PRIMARY KEY	(STUDENT_ID)
);

This query will create a table named tb_student, which you can confirm in the MySQL database.

Insert the six records into the table named tb_student using the following syntax of the INSERT query.

# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(1,'Ayush','Kumar', 'Male', 'Washington', 'akuman@yahoo.com', 2010);

Then select all data from the table to view using the following query.

# SQL Programming Using MySQL Version 8.27
SELECT * FROM test_db.tb_student

Your table will have the following data. You can also check on your end and compare.

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

After executing the above query, you will get the following result.

Observe the above output, and you will see that all the records are displayed, which are ordered by registration year (see the column within the green box). And the row_number is also the same as expected, starting from 1 and keeping increasing sequentially till the end of the table as we are reading all data from tb_student.

Use of ROW_NUMBER() in MySQL Using PARTITION BY Clause

We will only use the ROW_NUMBER() function with the PARTITION BY clause and observe the results. We’ll also compare this output with the results we got using ROW_NUMBER() with the ORDER BY clause.

Example Code:

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Now, you will get the following results.

Take a look at the REGISTRATION_YEAR column; it has 5 partitions (2010, 2011, 2012, 2013, and 2014). There are two rows in the table for partition 2010, and row numbers are assigned correctly (see the above screenshot again). There is only one row for partition 2011, 2012, 2013, 2014; that is why you can see 1 in the row_numb column.

If we are using the PARTITION BY clause, then why is the column named REGISTRATION_YEAR in ascending order? Because the PARTITION BY clause orders the data within those partitions. Let’s insert another record for which the value of REGISTRATION_YEAR would be 2009 and observe the results.

# SQL Programming Using MySQL Version 8.27

INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(7,'Mashal','Naaz', 'Female', 'Florida', 'mashalnaaz@gmail.com', 2009);

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Now, you will see that the recent record is at the top.

Use of ROW_NUMBER() in MySQL Using PARTITION BY and ORDER BY Clause

Now, we will only use the ROW_NUMBER() function with the PARTITION BY and ORDER BY clauses and see if it still provides the correct row numbers.

Example Code:

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_YEAR ORDER BY REGISTRATION_YEAR) AS row_numb
FROM test_db.tb_student;

After executing the above query, you will see the output same as we got using ROW_NUMBER() with PARTITION BY clause. See the following screenshot:

See the column with yellow background, this is what we were expecting. Here, we will use session variable to assign row numbers correctly.

Replication of ROW_NUMBER() in MySQL Using Session Variable

MySQL does not provide the correct ranking functionality when we simultaneously use PARTITION BY and ORDER BY clauses. In this scenario, we emulate this using Session Variable. Session variables are user-defined; you can see it here for detailed information.

Example Code:

# SQL Programming Using MySQL Version 8.27
SET @row_numb = 0;
SELECT *,
    (@row_numb:=@row_numb + 1) AS row_numb
FROM test_db.tb_student ORDER BY REGISTRATION_YEAR;

As you can see below, row_numb starts from 1 and consecutively increases.

How is it working? We first set a session variable row_numb using @ prefix and initialize with the 0. Then we selected the data from the table, ordered it, and printed it. (@row_numb:=@row_numb + 1) is just like incrementing and updating the variable’s value.

Conclusion

In this light of the above discussion, we have concluded that although we can use the ROW_NUMBER() functionin MySQL as well if we have Version 8.0 or above still there are some situations where we have to use Session Variables for ranking purposes.