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
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