How to distribute ranking using MySQL Query and PHP
- by nkp
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.