Re: your ranked out #x of X accounts?

[laptop alias]

As GD says, you would normally handle that at the application level.

But, just for fun, you could do something like this...

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),
('Brian',12);

SELECT a.user
     , a.kills
     , CONCAT('You are ranked '
             ,IF(b.user IS NULL,CONCAT('#',a.rank)
                               ,CONCAT('joint #',a.rank,' (along with ',COUNT(b.user),' more)')),' out of ',(SELECT COUNT(*) FROM user_kills)) 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
 GROUP
    BY a.user
 ORDER
    BY kills DESC;

+--------+-------+------------------------------------------------------+
| user   | kills | rank                                                 |
+--------+-------+------------------------------------------------------+
| Paul   |    15 | You are ranked #1 out of 5                           |
| George |    12 | You are ranked joint #2 (along with 2 more) out of 5 |
| John   |    12 | You are ranked joint #2 (along with 2 more) out of 5 |
| Brian  |    12 | You are ranked joint #2 (along with 2 more) out of 5 |
| Ringo  |     8 | You are ranked #5 out of 5                           |
+--------+-------+------------------------------------------------------+

(I should point out that, in both cases, on larger datasets - really anything over a couple of thousand rows - and even with the necessary modifications to @@group_concat_max_len, the OUTER JOIN will render these queries unusable!)



Edited 1 time(s). Last edit at 07/22/2011 01:54AM by laptop alias.