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.
If you want to update the table you can do:
UPDATE tbRank t
INNER JOIN (
SELECT ID, StudID, Score, `Time`, @rownum := @rownum + 1 AS rank
FROM (
SELECT ID, StudID, Score, `Time`
FROM tbRank
WHERE date = date(now())
ORDER BY score DESC, `time` ASC
) a
JOIN (
SELECT @rownum := 0
) r
) b ON b.id = t.id
SET t.rank = b.rank
But since you would do this every day, you would have to update all rows of rank column to null before executing this.
I wouldn't advise this approach because if you change one of them or insert a new record, you have to do the update again.
You can simply use this query whenever you want to get the ranking:
SELECT ID, StudID, Score, `Time`, @rownum := @rownum + 1 AS rank
FROM (
SELECT ID, StudID, Score, `Time`
FROM tbRank
WHERE date = date(now())
ORDER BY score DESC, `time` ASC
) a
JOIN (
SELECT @rownum := 0
) r
Additionally, if you want to know the rank of a specific student, you can do the following query:
SELECT * FROM
(
SELECT ID, StudID, Score, `Time`, @rownum := @rownum + 1 AS rank
FROM (
SELECT ID, StudID, Score, `Time`
FROM tbRank
WHERE date = date(now())
ORDER BY score DESC, `time` ASC
) a
JOIN (
SELECT @rownum := 0
) r
) b
WHERE b.StudID = 11;
So, this is your table :
ID StudID Score Time
1 11 8 60
2 22 6 45
3 33 4 76
4 44 6 67
5 55 8 35
This is the query to build the Rank:
SELECT * FROM tbRank GROUP BY Score DESC, Time ASC
It will 'rearrange' the row of your table with the highest score and fastest time will be arranged from top to bottom