MySQL ROW_NUMBER Function with Examples


How to Use the ROW_NUMBER Function in MySQL

In this tutorial, you'll learn how to use the MySQL ROW_NUMBER function.

ROW_NUMBER returns the number of the current record within its partition of a result set.

MySQL ROW_NUMBER() Syntax

The syntax of the ROW_NUMBER() function is as follows:

ROW_NUMBER() OVER(
PARTITION BY expression [, expression], …
ORDER BY expression [ASC DESC], [, expression], …
)

Remarks

  • Records numbers range from 1 to the number of partition records.
  • ROW_NUMBER() assigns a sequential number (different number) to each record. 
  • Use RANK() if you want to assign the same number.

Examples

Demo data:

studentx_scores:

idstudent_codestudent_namescore
1#ID_0001Student A98
2#ID_0002Student B89
3#ID_0003Student C65
4#ID_0004Student D55
5#ID_0005Student E65
6#ID_0006Student F80
7#ID_0007Student G98
8#ID_0008 Student H65

studenty_score:

idstudent_codestudent_namesubjectscore
1#ID_0001Student AChemistry78
2#ID_0002Student BChemistry75
3#ID_0003Student CChemistry78
4#ID_0004Student DChemistry59
5#ID_0005Student EChemistry59
6#ID_0006Student FChemistry75
7#ID_0007Student GChemistry68
8#ID_0008Student HChemistry95
9#ID_0001Student APhysics98
10#ID_0002Student BPhysics95
11#ID_0003Student CPhysics95
12#ID_0004Student DPhysics78
13#ID_0005Student EPhysics75
14#ID_0006Student FPhysics65
15#ID_0007Student GPhysics65
16#ID_0008Student HPhysics78
17#ID_0001Student AMaths90
18#ID_0002Student BMaths98
19#ID_0003Student CMaths70
20#ID_0004Student DMaths85
21#ID_0005Student EMaths80
22#ID_0006Student FMaths90
23#ID_0007Student GMaths98
24#ID_0008Student HMaths75

Example 1

The following statement selects records from the studentx_scores table, sorts the score column in ascending order, and assigns the sequence number of records:

SELECT ROW_NUMBER() OVER (ORDER BY score) AS ROW_NUMBER, student_code, student_name, score
FROM studentx_scores
ORDER BY ROW_NUMBER;

Result set:

ROW_NUMBERstudent_codestudent_namescore
1#ID_0004Student D55
2#ID_0003Student C65
3#ID_0008Student H65
4#ID_0005Student E65
5#ID_0006Student F80
6#ID_0002Student B89
7#ID_0007Student G98
8#ID_0001Student A98

Example 2

The following statement selects records from the studentx_scores table, sorts the score column in descending order, and assigns the sequence number of records:

SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS ROW_NUMBER, student_name, score
FROM studentx_scores
ORDER BY ROW_NUMBER;

Result set:

ROW_NUMBERstudent_codestudent_namescore
1#ID_0007Student G98
2#ID_0001Student A98
3#ID_0002Student B89
4#ID_0006Student F80
5#ID_0003Student C65
6#ID_0008Student H65
7#ID_0005Student E65
8#ID_0004Student D55

Example 3

The following statement selects records from the studenty_scores table, partitioned by the subject column, sorts the score column in descending order, and assigns the sequence number of records:

SELECT ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS ROW_NUMBER, student_name, subject, score
FROM studenty_scores
ORDER BY subject, ROW_NUMBER;

Result set:

ROW_NUMBERstudent_codestudent_namesubjectscore
1#ID_0008Student HChemistry95
2#ID_0001Student AChemistry78
3#ID_0003Student CChemistry78
4#ID_0002Student BChemistry75
5#ID_0006Student FChemistry75
6#ID_0007Student GChemistry68
7#ID_0004Student DChemistry59
8#ID_0005Student EChemistry59
1#ID_0002Student BMaths98
2#ID_0007Student GMaths98
3#ID_0001Student AMaths90
4#ID_0006Student FMaths90
5#ID_0004Student DMaths85
6#ID_0005Student EMaths80
7#ID_0008Student HMaths75
8#ID_0003Student CMaths70
1#ID_0001Student APhysics98
2#ID_0002Student BPhysics95
3#ID_0003Student CPhysics95
4#ID_0004Student DPhysics78
5#ID_0005Student EPhysics78
6#ID_0006Student FPhysics75
7#ID_0007Student GPhysics65
8#ID_0008Student HPhysics65

In this tutorial, we've learned how to use the MySQL ROW_NUMBER function. 

ROW_NUMBER returns the number of the current record within its partition of a result set. 

Record numbers range from 1 to the number of partition records. ROW_NUMBER assigns a sequential number to each record.

If you want to assign the same values, use RANK().

Leave a Reply

Your email address will not be published. Required fields are marked *