Wednesday, 7 August 2013

MySQL rank calculation with all entries at 0

MySQL rank calculation with all entries at 0

I have the following SQL script which ranks a set of users in a
leaderboard table.
UPDATE leaderboard
JOIN (SELECT f.winnings ,
IF (@lastPoint <> f.winnings,
@curRank := @curRank +1,
@curRank) AS rank,
@lastPoint := f.winnings
FROM leaderboard f
JOIN (SELECT @curRank := 0, @lastPoint := 0) r
WHERE f.competition =5
ORDER BY f.winnings DESC
) ranks ON (ranks.winnings = leaderboard.winnings)
SET leaderboard.rank = ranks.rank WHERE leaderboard.id =89;
However when all users have a rank of 0 it does not rank them as 1 (i.e
equal) this behaviou only occurs for 0.
winnings rank
0 0
0 0
0 0
Would anyone know how to do this..?
winnings rank
0 1
0 1
0 1
SQL FIDDLE http://sqlfiddle.com/#!2/53c3a/1

No comments:

Post a Comment