How can I update the rank column in the below table for;
just one category? This is to be done when a user wants to see the rankings for a specific category. In this case the rankings for the user himself, the top 10 users and the user’s selected friends will be shown together.
for all categories individually? This is to be done when a user wants to see his own rankings in all categories.
There will be around 100 categories and the number of users is around 10 000 and growing.
category ; username ; score ; rank
run ; bill ; 10 ; 1
run ; joe ; 7 ; 2
run ; sue ; 4 ; 3
jump ; sue ; 12 ; 1
jump ; bill ; 6 ; 2
How regular does an update happen, as if the rank is constantly changing you might have to use another method that doesnt require re-calculating the whole list.
Something LIKE this maybe your answer if you are updating one category at a time
SET @rowCount := 0;
SELECT category, username, score, (@rowCount :=@rowCount + 1) AS rank
from RankTable where category = '%%CATEGORY%%' ORDER BY score DESC
You can use this live, or use it to update this or another table to cache your ranks.
Hope this helps in some way, its the only easy and fast way I can think of at this moment in time.
Rank was included in the result using your code and when I was looking for a way to update the rank column in the original table I found this variant that does this and also checks for ties.
UPDATE MyTable
JOIN (SELECT p.username,
IF(@lastScore <> p.score,
@curRank := @curRank + 1,
@curRank) AS rank,
IF(@lastScore = p.score,
@curRank := @curRank + 1,
@curRank),
@lastScore := p.score
FROM MyTable p
JOIN (SELECT @curRank := 0, @lastScore := 0) r
ORDER BY p.score DESC
) ranks ON (ranks.username = MyTable.username)
SET MyTable.rank = ranks.rank;
This works fine if I have a table without different categories. But if I add the category column and limit the select to one category I get an '#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY p.score DESC ) ranks ON (ranks.id = MyTable.id) SET '. (I also used the id column here instead of the username column since there are multiple rows with the same username).
UPDATE MyTable
JOIN (SELECT p.id,
IF(@lastScore <> p.score,
@curRank := @curRank + 1,
@curRank) AS rank,
IF(@lastScore = p.score,
@curRank := @curRank + 1,
@curRank),
@lastScore := p.score
FROM MyTable p WHERE category = 'run'
JOIN (SELECT @curRank := 0, @lastScore := 0) r
ORDER BY p.score DESC
) ranks ON (ranks.id = MyTable.id)
SET MyTable.rank = ranks.rank;
The code specified in the error message seems to be the same so any ideas why I get this error message?
This update will happen whenever a user wants to see a highscore list but the user needs to click a link manually for this. When can updates like this start causing problems. When it’s 10 every minute or when it’s 10 000 every minute? The update will sometimes be done for all categories individually and sometimes for just one category. I have a regular web hosting account.
Thank you very much for correcting WHERE! As you’ve noticed I’m pretty new at this. Now it seems to be working fine. Do you know if I need to run a PHP loop if I want all categories to be ranked?
Thank you also for the lastRank addition. I can be a nice addition to the list and I will try to add it.
What is the score for?
Does it affect one category at a time ?
If it does, you only need to update that category once an update to a score is done, meaning when a user looks up there score, its a simple row fetch and now heavy sql processing.
It’s an online quiz and I think the score will be updated more often than the highscore is viewed. But since updating the rankings for one single category seems to be a fast query it’s probably better to, as you say, do this every time a score changes rather than updating the rankings for all categories when someone wants to view the scores.
I will continue and implement this. Thank you again for your kind help with this. I was trying many different solutions but as a beginner it is easy to get stuck when adapting schoolbook examples to ‘real life’.
