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:
| id | student_code | student_name | score |
| 1 | #ID_0001 | Student A | 98 |
| 2 | #ID_0002 | Student B | 89 |
| 3 | #ID_0003 | Student C | 65 |
| 4 | #ID_0004 | Student D | 55 |
| 5 | #ID_0005 | Student E | 65 |
| 6 | #ID_0006 | Student F | 80 |
| 7 | #ID_0007 | Student G | 98 |
| 8 | #ID_0008 | Student H | 65 |
studenty_score:
| id | student_code | student_name | subject | score |
| 1 | #ID_0001 | Student A | Chemistry | 78 |
| 2 | #ID_0002 | Student B | Chemistry | 75 |
| 3 | #ID_0003 | Student C | Chemistry | 78 |
| 4 | #ID_0004 | Student D | Chemistry | 59 |
| 5 | #ID_0005 | Student E | Chemistry | 59 |
| 6 | #ID_0006 | Student F | Chemistry | 75 |
| 7 | #ID_0007 | Student G | Chemistry | 68 |
| 8 | #ID_0008 | Student H | Chemistry | 95 |
| 9 | #ID_0001 | Student A | Physics | 98 |
| 10 | #ID_0002 | Student B | Physics | 95 |
| 11 | #ID_0003 | Student C | Physics | 95 |
| 12 | #ID_0004 | Student D | Physics | 78 |
| 13 | #ID_0005 | Student E | Physics | 75 |
| 14 | #ID_0006 | Student F | Physics | 65 |
| 15 | #ID_0007 | Student G | Physics | 65 |
| 16 | #ID_0008 | Student H | Physics | 78 |
| 17 | #ID_0001 | Student A | Maths | 90 |
| 18 | #ID_0002 | Student B | Maths | 98 |
| 19 | #ID_0003 | Student C | Maths | 70 |
| 20 | #ID_0004 | Student D | Maths | 85 |
| 21 | #ID_0005 | Student E | Maths | 80 |
| 22 | #ID_0006 | Student F | Maths | 90 |
| 23 | #ID_0007 | Student G | Maths | 98 |
| 24 | #ID_0008 | Student H | Maths | 75 |
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_NUMBER | student_code | student_name | score |
| 1 | #ID_0004 | Student D | 55 |
| 2 | #ID_0003 | Student C | 65 |
| 3 | #ID_0008 | Student H | 65 |
| 4 | #ID_0005 | Student E | 65 |
| 5 | #ID_0006 | Student F | 80 |
| 6 | #ID_0002 | Student B | 89 |
| 7 | #ID_0007 | Student G | 98 |
| 8 | #ID_0001 | Student A | 98 |
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_NUMBER | student_code | student_name | score |
| 1 | #ID_0007 | Student G | 98 |
| 2 | #ID_0001 | Student A | 98 |
| 3 | #ID_0002 | Student B | 89 |
| 4 | #ID_0006 | Student F | 80 |
| 5 | #ID_0003 | Student C | 65 |
| 6 | #ID_0008 | Student H | 65 |
| 7 | #ID_0005 | Student E | 65 |
| 8 | #ID_0004 | Student D | 55 |
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_NUMBER | student_code | student_name | subject | score |
| 1 | #ID_0008 | Student H | Chemistry | 95 |
| 2 | #ID_0001 | Student A | Chemistry | 78 |
| 3 | #ID_0003 | Student C | Chemistry | 78 |
| 4 | #ID_0002 | Student B | Chemistry | 75 |
| 5 | #ID_0006 | Student F | Chemistry | 75 |
| 6 | #ID_0007 | Student G | Chemistry | 68 |
| 7 | #ID_0004 | Student D | Chemistry | 59 |
| 8 | #ID_0005 | Student E | Chemistry | 59 |
| 1 | #ID_0002 | Student B | Maths | 98 |
| 2 | #ID_0007 | Student G | Maths | 98 |
| 3 | #ID_0001 | Student A | Maths | 90 |
| 4 | #ID_0006 | Student F | Maths | 90 |
| 5 | #ID_0004 | Student D | Maths | 85 |
| 6 | #ID_0005 | Student E | Maths | 80 |
| 7 | #ID_0008 | Student H | Maths | 75 |
| 8 | #ID_0003 | Student C | Maths | 70 |
| 1 | #ID_0001 | Student A | Physics | 98 |
| 2 | #ID_0002 | Student B | Physics | 95 |
| 3 | #ID_0003 | Student C | Physics | 95 |
| 4 | #ID_0004 | Student D | Physics | 78 |
| 5 | #ID_0005 | Student E | Physics | 78 |
| 6 | #ID_0006 | Student F | Physics | 75 |
| 7 | #ID_0007 | Student G | Physics | 65 |
| 8 | #ID_0008 | Student H | Physics | 65 |
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().