How to distribute ranking using MySQL Query and PHP

Posted by nkp on Stack Overflow See other posts from Stack Overflow or by nkp
Published on 2013-11-09T02:28:50Z Indexed on 2013/11/09 3:54 UTC
Read the original article Hit count: 133

Filed under:
|
|

I have to distribute ranking to the 5000 students in an exam. Ranking is based on the score and the time taken (in seconds) to obtain that score.

For example is 5 students have same score, then taken will be the criteria to calculate their ranks otherwise score should be the criteria to calculate their ranks.

Following is my table tbRank

ID  StudID  Score Time Date        Rank
1   11      8     60   09-11-2013 
2   22      6     45   09-11-2013
3   33      4     76   09-11-2013
4   44      6     67   09-11-2013
5   55      8     35   09-11-2013 
6   66      8     35   08-11-2013
7   77      8     39   08-11-2013

Now rank column in above table should be updated as:

ID  StudID  Score Time Date        Rank
1   11      8     60   09-11-2013  2
2   22      6     45   09-11-2013  3
3   33      4     76   09-11-2013  5
4   44      6     67   09-11-2013  4
5   55      8     35   09-11-2013  1
6   66      8     35   08-11-2013  1
7   77      8     39   08-11-2013  2

I want to make a MySQL Query to do this business. Similarly there can be more than 10000 records in the table. So I need an optimized query for this functionality.

Note: I am using PHP and MYSQL.

Update: Everyday almost 5000 new entries will be created in the table and after all insertions are made, rank column will be updated once in a day. Now please suggest me the best way to do this. If I update rank column in the table, then only once I will have to do it, otherwise everytime while fetching the rank of the student, I will have to make calculations.

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql