Ok, ok, I'll debug my code...
CREATE TABLE score_ranked (
gamer VARCHAR(9) NOT NULL,
score INT NOT NULL
);
INSERT INTO score_ranked VALUES
('Alp', 123),
('Bob', 111),
('Ben', 111),
('Dan', 77),
('Ira', 66),
('Jay', 55),
('Jon', 55),
('Jil', 55),
('May', 44),
('Wes', 33),
('Won', 33),
('Zoe', 11); # off the end
# points: 123 111 111 77 66 55 55 55 44 33 33
# Rank: 1 2 2 4 5 6 6 6 9 10 10 -- desired output?
# @seq: 1 2 3 4 5 6 7 8 9 10 11 -- helper value
SELECT @seq := 0, -- raw 1,2,3,...
@rank := 0, -- rank, allowing for ties
@prev := 999999999; -- for catching ties
SELECT Rank, gamer, point
FROM (
SELECT @seq := @seq + 1 AS seq,
@rank := IF(@prev = point, @rank, @seq) AS Rank,
@prev := point as prev, # I forgot this
gamer,
point
FROM (
SELECT gamer, SUM(score) as point
FROM score_ranked s
GROUP BY gamer
ORDER BY point DESC
) x
) y
WHERE Rank <= 10
ORDER BY Rank, gamer;
+------+-------+-------+
| Rank | gamer | point |
+------+-------+-------+
| 1 | Alp | 123 |
| 2 | Ben | 111 |
| 2 | Bob | 111 |
| 4 | Dan | 77 |
| 5 | Ira | 66 |
| 6 | Jay | 55 |
| 6 | Jil | 55 |
| 6 | Jon | 55 |
| 9 | May | 44 |
| 10 | Wes | 33 |
| 10 | Won | 33 |
+------+-------+-------+
11 rows in set (0.00 sec)
Or, you might prefer
SELECT @seq := 0, -- raw 1,2,3,...
@rank := 0, -- rank, allowing for ties
@prev := 999999999; -- for catching ties
SELECT Rank,
GROUP_CONCAT(gamer ORDER BY gamer SEPARATOR ', ') AS Gamers,
point
FROM (
SELECT @seq := @seq + 1 AS seq,
@rank := IF(@prev = point, @rank, @seq) AS Rank,
@prev := point as prev,
gamer,
point
FROM (
SELECT gamer, SUM(score) as point
FROM score_ranked s
GROUP BY gamer
ORDER BY point DESC
) x
) y
WHERE Rank <= 10
GROUP BY Rank
ORDER BY Rank, Gamers;
+------+---------------+-------+
| Rank | Gamers | point |
+------+---------------+-------+
| 1 | Alp | 123 |
| 2 | Ben, Bob | 111 |
| 4 | Dan | 77 |
| 5 | Ira | 66 |
| 6 | Jay, Jil, Jon | 55 |
| 9 | May | 44 |
| 10 | Wes, Won | 33 |
+------+---------------+-------+