This is covered elsewhere (Bouman, Shlomi, et al.) but just to elaborate on what Ronan said...
DROP TABLE IF EXISTS user_kills;
CREATE TABLE user_kills
(user VARCHAR(12) NOT NULL PRIMARY KEY
,kills INT NOT NULL
);
INSERT INTO user_kills VALUES
('John',12),
('Paul',15),
('George',12),
('Ringo',8);
SELECT * FROM user_kills;
+--------+-------+
| user | kills |
+--------+-------+
| John | 12 |
| Paul | 15 |
| George | 12 |
| Ringo | 8 |
+--------+-------+
SELECT DISTINCT
a.user
, a.kills
, CONCAT(IF(b.user IS NULL,' ','='),a.rank) rank
FROM
( SELECT user
, kills
, @prev := @curr
, @curr := kills
, @rank := IF(@prev = @curr, @rank, @rank+1) rank
FROM user_kills
JOIN
( SELECT @curr := null
, @prev := null
, @rank := 0
) x
ORDER
BY kills DESC
) a
LEFT
JOIN user_kills b
ON b.kills = a.kills
AND a.user <> b.user
WHERE a.user = 'George';
+--------+-------+------+
| user | kills | rank |
+--------+-------+------+
| George | 12 | =2 |
+--------+-------+------+
Note that this ranks like this:
1,2,2,3
If you want this...
1,2,2,4
...then consider something like this...
SELECT DISTINCT
a.user
, a.kills
, CONCAT(IF(b.user IS NULL,' ','='),a.rank) rank
FROM
( SELECT user
, kills
, FIND_IN_SET(kills, (SELECT GROUP_CONCAT(kills ORDER BY kills DESC) FROM user_kills)) rank
FROM user_kills
) a
LEFT
JOIN user_kills b
ON b.kills = a.kills
AND a.user <> b.user
ORDER
BY kills DESC;
Edited 5 time(s). Last edit at 07/21/2011 06:10AM by laptop alias.