JIYIK CN >

In this tutorial, we will explain how to use the VALUES function in MySQL ROW_NUMBER(). This is a sorting method that assigns consecutive numbers within a partition starting from 1. It is important to note that no two rows within a partition have the same number.

We will also see how PARTITION BYAND ORDER BYaffects MySQL results. You must use ORDER BYthe WITH clause ROW_NUMBER()as it is mandatory. However PARTITION BYthe STRING clause is optional.

If you use both the PARTITION BYand ORDER BYclauses at the same time, the result will be undefined. Here, we will see how to use session variables to simulate ROW_NUMBER()the function to get the desired result.

Note that ROW_NUMBER()this is not available prior to MySQL version 8.0. You can see what's new in MySQL version 8.0 here.

ROW_NUMBER()Using the Using ORDER BYClause in MySQL

We will just use the function with ORDER BYthe while ROW_NUMBER()clause and observe the results. Let us first create the table and fill it with some data.

Sample 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 tb_studenttable called , which you can confirm in your MySQL database.

Use INSERTthe following syntax of query to insert six records into a table named tb_student.

# 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 the data from the table to view it using the following query.

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

Your table will contain the following data. You can also check 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.

Looking at the output above, you can see that all the records are displayed, sorted by the year of registration (see the green box column). And row_numberis also as expected, starting at 1 and tb_studentincreasing as we read all the data from till the end of the table.

In MySQL, use PARTITION BYthe clauseROW_NUMBER()

We will use only the function with PARTITION BYthe clause ROW_NUMBER()and observe the result. We will also compare this output with the results obtained using the ROW_NUMBER()and clauses.ORDER BY

Sample 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 result.

Look at REGISTRATION_YEARthe column; it has 5 partitions ( 2010, 2011, 2012, 2013and 2014). partitionThere are two rows in the table for 2010, and the row numbers are assigned correctly (again, see the screenshot above). partitionThere is only one row for 2011, 2012, 2013, 2014; that's why you can row_numbsee in the column 1.

If we use PARTITION BYthe INSERT clause, then why is REGISTRATION_YEARthe column named in ascending order? Because PARTITION BYthe INSERT clause sorts the data in these partitions. Let us insert another record with REGISTRATION_YEARthe value of 2009 and observe the result.

# 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;

You will now see the most recent records at the top.

In MySQL, use PARTITION BYthe and ORDER BYclausesROW_NUMBER()

Now, we will just use the function with the PARTITION BYand ORDER BYclauses ROW_NUMBER()to see if it still gives the correct row numbers.

Sample 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 same output as using the ROW_NUMBER()and PARTITION BYclauses. See the following screenshot:

You see the columns with yellow background, which is what we expect. Here we will use session variables to assign row numbers correctly.

Using Session Variables in MySQL ReplicationROW_NUMBER()

MySQL does not provide correct ranking functionality when we use both PARTITION BYand clauses. In this case, we simulate it using . Session variables are user defined; you can see the details here.ORDER BY会话变量

Sample 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_numbit starts at 1 and increases continuously.

How does it work? We first set the session variable using the @ prefix row_numband initialize it with 0. Then we select the data from the table, sort it and print it. (@row_numb:=@row_numb + 1)Just like incrementing and updating the value of the variable.

in conclusion

From the above discussion, we conclude that although we can use function in MySQL ROW_NUMBER()if we have version 8.0 or higher, but in some cases we have to use Session Variablesfor ranking purpose.