September 14th, 2012, 04:52 PM
Adding a line to reset rank back to 1
This is how I rank the players in tournaments that I host:
IF(@LastScore <> Score, @CurrentRank := @CurrentRank + @NextRank, @CurrentRank) AS Rank, Player, Games,
IF(@LastScore = Score, @NextRank := @NextRank + 1, @NextRank := 1) AS AddToGetNextRank,
@LastScore := Score AS Score, @LastGames := Games AS Games
(SELECT @CurrentRank := 1, @NextRank := 0, @LastScore := 0) p
What I would like to add to this query is the ability to reset the CurrentRank to 1 if "Games" changes.
Because the data is ORDERed BY Score DESC, it is guaranteed that Games never increases from row n to row n+1. In other words, Games will decrease or stay the same.
September 14th, 2012, 05:28 PM
I was thinking about a line:
But I couldn't get it to work...
IF(@LastGames > Games, @CurrentRank := 1, @NextRank := 1) AS ResetRank,