By reading around, this seems to be a reasonable way of ranking people.
The table:
UserId (int primary key)
Name (varchar(100))
Score1 (int highest scores rank 1)
Score2 (int, if Score1s are equal, the person with the highest Score2 trumps all other equal Score1s)
Rank (int this is what I want to fill)
The Rank will only change about once a day. However, the page will likely be viewed maybe 1000 times a day. So instead of getting the database to calculate the rank for each of the 1000 visits, I thought it would be best to just fill the 'Rank' column once.
How can I include an UPDATE to fill the Rank column, using my chosen way to rank people. (Note if two people are ranked 5, then the next rank is 7.)
SELECT UserId, Name, Score, Rank FROM
(SELECT UserId, Name, Score1, Score2
@curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank,
@incRank := @incRank + 1,
@prevScore1 := Score1,
@prevScore2 := Score2
FROM players p,
(SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r
ORDER BY Score1 ?????
)
s
The Questions are:
- How do I UPDATE Rank in the table within this SQL statement?
- What do I do in the ????? ORDER BY statement to make sure it orders by Score1 first then when these are equal it orders by Score2 second?