Let’s say we have a game where each will give be move into level or the chances of getting a score among 2+ users is high.
I’ll frequently get into tie when 2+ users get the same score so I’ll be confused to whom I should list in leaderboard. It’s logical to list the user who have achieved a particular level/score in fewer attempts. The idea is to attach the attempt no. to all the rows for each user for all their games and sort it by user_id, level (or score), attempt_no and then date. We then group the result by user_id to get only the least attempt for their latest achievement in level or greatest score. Now we would have got each user’s maximum level (or score) along with their attempt no. and it is straightforward to sort the result.
Consider the following table,
CREATE TABLE `users_games` (</code></pre> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT(10) UNSIGNED NOT NULL, `level_id` TINYINT(3) UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
We store the user_id, their level and the date when they have achieved the level.
The complete query to the problem
</code></pre> SET @attemptnum = 1, @prev_val = NULL, @rownum = 0 ; SELECT @rownum := @rownum + 1 AS rank, id, user_id, attempt, level_id, DATE FROM ( SELECT id, user_id, attempt, level_id, DATE FROM ( SELECT id, user_id, level_id, IF(@prev_val = user_id, @attemptnum := @attemptnum + 1, @attemptnum := 1) AS attempt, @prev_val := user_id, DATE FROM ( SELECT id, user_id, level_id, DATE FROM users_games ORDER BY user_id ASC, level_id, DATE ASC) rs)rs1 GROUP BY user_id ORDER BY level_id DESC, attempt ASC, DATE ASC )rs2 ORDER BY level_id DESC, attempt ASC, DATE ASC
The inner most query ensures the users_games table is sorted by user_id, then their level, then by date. Then, we attach attempt_now for each row by checking through their levels. Note, a user can be in the same level for many times and will be counted as attempts to achieve their max level.
We further sort the result by level_id by DESC, their attempt, then date. The date is to break any tie, if 2+ users have achived the same level in the same attempt. In that case, we give appreciation to the user who have done it first.
We group the result by user_id to keep only the first row for each user that will contain their best level and their attempt, ordering it again by level_id DESC, attempt ASC to ensure the desire result.
SenG's personal blog 