MySQL Forums
Forum List  »  Newbie

Re: your ranked out #x of X accounts?
Posted by: laptop alias
Date: July 21, 2011 05:17AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: your ranked out #x of X accounts?
July 21, 2011 05:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.