0

I have students exam scores for a specific subject in database. Students are given a test and they may get a score of 0 - more than 100.

I would would like to rank students based on their score. Here is the simple score. 

MariaDB [phpa]> select id, name, score FROM users WHERE id < 10;
+----+---------------+-------+---------+-
| id | name          | score |  rank   | 
+----+---------------+-------+---------+-
|  1 | yngiid        |    97 |         | 
|  2 | viyrp         |   217 |         | 
|  3 | pae           |   599 |         | 
|  4 | spohl         |   284 |         | 
|  5 | shl           |   295 |         | 
|  6 | okeer         |    73 |         | 
|  7 | jmaany        |   657 |         | 
|  8 | hxt           |    80 |         | 
|  9 | yanier        |   599 |         | 
+----+---------------+-------+---------+-

My expected output is for the rank to be 

+----+---------------+-------+---------+-
| id | name          | score |  rank   | 
+----+---------------+-------+---------+-
|  7 | jmaany        |   657 |   1     | 
|  3 | pae           |   599 |   2     | 
|  9 | yanier        |   599 |   2     | 
|  5 | shl           |   295 |   3     | 
|  4 | spohl         |   284 |   4     | 
|  2 | viyrp         |   217 |   5     |         
|  1 | yngiid        |    97 |   6     | 
|  8 | hxt           |    80 |   7     |     
|  6 | okeer         |    73 |   8     | 
+----+---------------+-------+---------+-

Now, all I need is for the rank to from 1 - 9 but based on the score. The greater the score the lower the rank. as in jmaay would get 1 and okeer would be 9.

To achive this, I tried to do something like this 

get = conn.query("SELECT name, score FROM users GROUP BY score ORDER BY score DESC")
users = get.fetchall()

rank = 0
for user in users:
   rank += 1
   conn.prepare("UPDATE users SET rank = %s WHERE name = %s", rank, user[0])

The above will iterate the users based on the score and rank them from 1. But, the problem is that since the i increments on every loop, users who have the same score will get different ranking as in the example of pae and yanier both bound to get the score of 2 and 3 instead of just 2 for both.

So, to sum it up, how do I give every user a fair number of rank

trbvm
  • 453
  • 1
  • 4
  • 7
  • Do you want ranks like `6, 7, 8, 8, 10, 11, ...`, `6, 7, 9, 9, 10, 11, ...` or `6, 7, 8, 8, 9, 10, ...` – Felk Nov 26 '15 at 18:46
  • @Felk I want to rank them from 1 - 9. the biggest score gets 1 then the least score gets 9. – trbvm Nov 26 '15 at 18:52
  • Yes, I was asking what should happen for a score equality. whether to duplicate the higher or the lower rank, or to give both the same rank without a padding. I'm not sure how to phrase this, please reread my examples – Felk Nov 26 '15 at 18:58
  • I am sorry. Same score, same rank. Your examples start from 6-11. I want the rank to start from 1 (for the biggest score) and go up from there. That's why I did not understand you – trbvm Nov 26 '15 at 19:00

1 Answers1

0

I think that you need something like this:

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT id, name, score, CASE
    WHEN @prev_value = score THEN @rank_count
    WHEN @prev_value := score THEN @rank_count := @rank_count + 1
END AS rank
FROM users
ORDER BY score DESC

Original answer can be found here.

Community
  • 1
  • 1
Kostas Mitsarakis
  • 4,772
  • 3
  • 23
  • 37
  • Ok, I have been going back and forth for the past hour with your script and all the provided solutions and this does not definitely work as intended. It only ranks when the script is run the first time, the second, third and afterwards it just ranks it differently. I think the variables are persisting. – trbvm Nov 26 '15 at 20:20
  • Why do you run the script many times? It needs to get executed only one time to sort the rows. – Kostas Mitsarakis Nov 26 '15 at 20:43
  • For the first time, it works but when I run the script again the ranks are very different. This is with the same data, no new content added to the database. It is like the `@rank_count` variable is not set to `0` when your run the query again. It seems like the hold on the the previous data. so, if the last user's rank was `1234` then when you run the query, it starts from that number. So, previously ranked `1` user will no longer be `1` but `1234 + 1` hence `1235` It is strange – trbvm Nov 26 '15 at 20:49
  • Close the connection before the script ends and see if there is a change. – Kostas Mitsarakis Nov 26 '15 at 21:21
  • I don't this that is it. After all, I am trying this from mysql workbench. It shows the same issue [this answer](http://stackoverflow.com/a/25550039/5609592) works without the above issue, and assigns the same rank to the same-score users, but still has some minor issues. I will go with it for now – trbvm Nov 26 '15 at 23:01
  • Probably it's a workbench issue and when you put it in code it will work fine. But if the other answer works it's fine too. – Kostas Mitsarakis Nov 27 '15 at 01:26
  • 1
    Nope, they were just not the answer I was looking for. I [got a very good answer](http://dba.stackexchange.com/questions/122226/mysql-logical-ranking-based-on-score) though. – trbvm Nov 27 '15 at 20:56