How to find nth rank in MySQL?

Last updated on September 11th, 2020 at 09:41 am

Many a time you need to find nth rank in MySQL. E.g find 3rd rank in a class of students. Students ranked by score. To find nth rank in MySQL, first we will rank the records. Then we filter the nth rank in MySQL. Here’s a query you can use. Just replace the table name scores and column names id, score.

Find nth rank in MySQL based on increasing value

Example:

CREATE TABLE score (id int, score int);
INSERT INTO scores VALUES (1, 35),(2, 10),(3,30),(4,22),(5,20),(6,18),(7,36);
Scores:
+----+------+
| id | score|
+----+------+
| 1  |   35 |
| 2  |   10 |
| 3  |   30 |
| 4  |   22 |
| 5  |   20 |
| 6  |   18 |
| 7  |   36 |
+----+------+

Here we find the record with rank 3. The following query ranks the records based on increasing score. Then it filters the record with rank 3:

SELECT * from (
SELECT    id,score,
          @curRank := @curRank + 1 AS rank
FROM      scores p, (SELECT @curRank := 0) r
ORDER BY  score)
WHERE rank = 3;

The

(SELECT @curRank := 0)

part allows the variable initialization without requiring a separate SET command.

Result:
+----+------+------+
| id | score| rank |
+----+------+------+
| 5  |   20 |    3 |
+----+------+------+
1 row in set (0.0 sec)

Find nth rank in MySQL based on decreasing value

If you want to find nth rank in MySQL based on decreasing order of scores just add the keyword DESC in query. Here we find the record with rank 3.

SELECT * from (
SELECT    id,score,
          @curRank := @curRank + 1 AS rank
FROM      scores p, (SELECT @curRank := 0) r
ORDER BY  score DESC)
WHERE rank=3;

Result:

+----+------+------+
| id | score| rank |
+----+------+------+
| 3  |   30 |    3 |
+----+------+------+
1 row in set (0.0 sec)

You can apply the above methods to get nth rank for any kind of metric such as salary, employee count, etc.