How to find nth rank in MySQL?


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)
mm

About Ubiq

Ubiq is a business dashboard & reporting platform for SMBs & enterprises. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!