MySQL Query to get rank of a student

Question

I have a table with the following structure.

CREATE TABLE `score` (
    `sid` INT NULL,
    `name` INT NULL,
    `rollno` INT NULL,
    `examid` INT NULL,
    `phone` INT NULL,
    `dob` INT NULL,
    `district` INT NULL,
    `score` INT NULL,
    `active` INT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I need a query that will assign rank to a student based on their score and age. If two students have same mark, student with highest date of birth is given priority.

This is the sample data I have:

--------------------------------------------------------------------------------
sid | name | examid | phone | dob       | district | score | date       | active 
--------------------------------------------------------------------------------
1     User1    1      12345  10/11/1983   209         10     10/11/2017     1

2     User2    1      34567  11/10/1983   209         10     10/11/2017     1

3     User3    1      34567  11/10/1985   209         20     10/11/2017     1
------------------------------------------------------------------------------

The result I am expecting is like this:

+----+------------------+----------------+
| id | name             |           rank |
+----+------------------+----------------+
|  2 |           User2  |              1 |
|  3 |           User3  |              2 |
|  2 |           User1  |              3 |

My question is similar to this question (Get the rank of a user in a score table) but is there any other solution.

This is the current query I have which is working but how to display rank with the same query.

SELECT name, rollno, score
FROM score
WHERE examid = '1'
ORDER BY score DESC, dob ASC

score 1 · Answer 1 · answered Jul 31 '18 at 08:02

MySQL 8+

SELECT id, name, ROW_NUMBER() OVER (ORDER BY score ASC, dob DESC) rank
FROM score

MySQL 5+

SELECT id, name, @rank := @rank + 1 rank
FROM score, (SELECT @rank := 0) init
ORDER BY score ASC, dob DESC

The students with equal both score and birth will be ranked randomly.

Lennart - Slava Ukraini · Accepted Answer · 2018-07-31T09:07:29.937

With the latest version of MySQL you can use a window function:

SELECT name, rollno, score
     , rank() over (order by score, dob desc) as rnk
FROM score
WHERE examid = '1'
ORDER BY score DESC, dob ASC

rank() will use gaps in case of a draw (1,1,3,...), use dense_rank() for 1,1,2,...

For older version you can count the number of scores less or equal to current score

SELECT name, rollno, score
     , (select count(1) + 1 from score s2
        where s2.score > s1.score
           or (s2.score = s1.score and s2.dob < s1.dob)) as rnk
FROM score s1
WHERE examid = '1'
ORDER BY score DESC, dob ASC

MySQL Query to get rank of a student

2 Answers2