0

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
1

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
  • When I use the second query rank count starts from 0. – user2634873 Jul 31 '18 at 8:18
  • just add 1 to the count, I'll update – Lennart Jul 31 '18 at 8:20
  • You can also add 1 outside of the sub-select: , (...) + 1 as rnk – Lennart Jul 31 '18 at 8:22
  • I have updated the query and it is now working. – user2634873 Jul 31 '18 at 8:23
  • For the query to work I have to replace '<' with '>' – user2634873 Jul 31 '18 at 8:59
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.