I'm building a multi-game tournament scoreboard, where many players can play many games, many times. I want to assign points to players per game based on their rank for each game, not their actual score.
e.g.
Donkey Kong
Rank | Player | Score | Points Awarded
1 | Player2 | 34,000 | 1,000
2 | Player1 | 32,000 | 999
3 | Player3 | 29,000 | 998
Robotron
Rank | Player | Score | Points Awarded
1 | Player1 | 39,000 | 1,000
2 | Player3 | 32,000 | 999
3 | Player2 | 21,000 | 998
Tournament Standings
Player1 - 1,999 Points
Player2 - 1,998 Points
Player3 - 1,997 Points
So far I have ranking and points calculations working just fine...
SELECT
`id`,
`userID`,
`gameID`,
`gamescore`,
`rank`,
1001.0 - (rank) AS points
FROM (
SELECT
`id`,
`userID`,
`gameID`,
`gamescore`,
@curr_rank := IF(@prev_rank = id, @curr_rank, @curr_rank + 1) AS rank,
@prev_rank := id
FROM
`submit_score`,
(SELECT @curr_rank := 0) y,
(SELECT @prev_rank := NULL) z
WHERE `submit_score`.`tournID` = 2
ORDER BY `gamescore` DESC
) ranked_game;
But I need to be able to assign the points by rank-per-game and then have a grand total of points for each player that I can then show in a list.