Retrieving row ranking in a table

Question

I'm aware there are already many questions alike however none of those answers helped me achieve row rankings in a table.

I have a table set up as so:

+----------------------------------+---------------+--------+
| HEX(UUID)                        | Username      | Rating |
+----------------------------------+---------------+--------+
| 0000F659C5854C9DB8BE8984FEC8CFD9 | Aidan8or_     |   1600 |
| 00022E3531F44C36AE058B3CF063C02F | rob160502     |   1600 |
| 000399B665D14979B3C0AF309112625F | HauntedCorpse |   1536 |
| 0003BAC2C0764C0C9FE20C4A3C701D7E | Glitchx0R     |   1234 |
| 000597056E564BF587DAA1D31E51E29F | jkz101022     |   455  |
+----------------------------------+---------------+--------+

What I would like to do here is select a single row with a ranking appended to it (1, 2, 3, 10, 100, ...). If you haven't already guessed it, the rating is an ELO Rating therefore many scores are tied and I need to accommodate for that when creating leaderboards.

I've taken a look at this: Get the rank of a user in a score table and it had some really good answers but I just couldn't get any of them working correctly.

I've tried all those solutions and got close with this:

SELECT HEX(UUID) as UUID, Username, Rating, FIND_IN_SET( Rating, (    
  SELECT GROUP_CONCAT( Rating
  ORDER BY Rating DESC ) 
  FROM table_name )
  ) AS rank
  FROM table_name
WHERE Username = 'jkz101022'

however returns the ranking as 0.

RolandoMySQLDBA · Answer 1 · 2014-08-25T21:38:37.553

You must evaluate everybody's rank and select the Username last

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,Username,rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT COUNT(1) scorecount,rating score
            FROM mytable GROUP BY rating
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN scores BB USING (score)
) A WHERE Username='jkz101022';

If you want the original row, then do this

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT COUNT(1) scorecount,rating score
            FROM mytable GROUP BY rating
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN scores BB USING (score)
) A INNER JOIN mytable B USING (Username)
WHERE A.Username='jkz101022';

UPDATE 2014-08-25 16:57 EDT

I Fixed My Code

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    (@curscore:=rating) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT DISTINCT Rating score FROM survivalgamesstats
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
) A INNER JOIN survivalgamesstats B USING (Username)
WHERE A.Username='jkz101022';

I got this outout

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='jkz101022';
+----------------------------------+-----------+--------+------+
| UUID                             | Username  | Rating | rank |
+----------------------------------+-----------+--------+------+
| 000597056E564BF587DAA1D31E51E29F | jkz101022 |   1600 |    2 |
+----------------------------------+-----------+--------+------+
1 row in set (0.00 sec)

mysql>

UPDATE 2014-08-25 17:15 EDT

I tried it with rob160502 and got this:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='rob160502';
+----------------------------------+-----------+--------+------+
| UUID                             | Username  | Rating | rank |
+----------------------------------+-----------+--------+------+
| 00022E3531F44C36AE058B3CF063C02F | rob160502 |   1650 |    1 |
+----------------------------------+-----------+--------+------+
1 row in set (0.00 sec)

mysql>

UPDATE 2014-08-25 17:38 EDT

I even tried HauntedCorpse and got this:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='HauntedCorpse';
+----------------------------------+---------------+--------+------+
| UUID                             | Username      | Rating | rank |
+----------------------------------+---------------+--------+------+
| 000399B665D14979B3C0AF309112625F | HauntedCorpse |   1536 |    5 |
+----------------------------------+---------------+--------+------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : Please note that the results come from your actual data in SQLFiddle. I simply loaded the data into a test database and ran my code against it.

score 1 · Answer 2 · answered Aug 25 '14 at 19:12

Hi and welcome to the forums. What version of MySQL are you running? I'm using 5.6.16 and, as far as I can see, your original query gives you exactly the result you want.

I did the following.

CREATE TABLE elo
(
  UUID VARCHAR(32),
  Username vARCHAR(25),
  Rating INT(10)
);

INSERT INTO elo VALUES
('0000F659C5854C9DB8BE8984FEC8CFD9', 'Aidan8or_', 1600),
('00022E3531F44C36AE058B3CF063C02F', 'rob160502' , 1600),
('000399B665D14979B3C0AF309112625F', 'HauntedCorpse', 1536),
('0003BAC2C0764C0C9FE20C4A3C701D7E', 'Glitchx0R', 1234),
('000597056E564BF587DAA1D31E51E29F', 'jkz101022', 455);

Ran the following query (yours without the WHERE clause)

SELECT HEX(UUID) as UUID, Username, Rating, FIND_IN_SET( Rating, (    
  SELECT GROUP_CONCAT( Rating
  ORDER BY Rating DESC ) 
  FROM elo )
  ) AS rank
  FROM elo;

And obtained these results

+------------------------------------------------------------------+---------------+--------+------+
| UUID                                                             | Username      | Rating | rank |
+------------------------------------------------------------------+---------------+--------+------+
| 3030303046363539433538353443394442384245383938344645433843464439 | Aidan8or_     |   1600 |    1 |
| 3030303232453335333146343443333641453035384233434630363343303246 | rob160502     |   1600 |    1 |
| 3030303339394236363544313439373942334330414633303931313236323546 | HauntedCorpse |   1536 |    3 |
| 3030303342414332433037363443304339464532304334413343373031443745 | Glitchx0R     |   1234 |    4 |
| 3030303539373035364535363442463538374441413144333145353145323946 | jkz101022     |    455 |    5 |
+------------------------------------------------------------------+---------------+--------+------+

With the WHERE clause, it gives a rank of 5 to user jkz101022.

In future, could you provide DDL (CREATE TABLE statements - you can run SHOW CREATE TABLE elo \G) and DML (INSERT INTO elo VALUES(...))either here or on SQLFiddle or similar - it helps those who are trying to help you :-)

ypercubeᵀᴹ · Answer 3 · 2015-01-17T23:28:06.477

Another way to get the ranking. There are different ways to assign rankings, I assume you want the RANK() function equivalent. For DENSE_RANK(), replace the COUNT(*) with COUNT(DISTINCT p.Rating):

For one user only:

SELECT 
    t.UUID, t.Username, t.Rating, 
    1 + ( SELECT COUNT(*) 
          FROM survivalgamesstats AS p 
          WHERE p.Rating > t.Rating
        ) AS Ranking 
FROM 
    survivalgamesstats AS t
WHERE 
    t.Username = 'jkz101022' ;

For all users (I don't expect this to be efficient in large tables):

SELECT 
    t.UUID, t.Username, t.Rating, 
    1 + ( SELECT COUNT(*) 
          FROM survivalgamesstats AS p 
          WHERE p.Rating > t.Rating
        ) AS Ranking 
FROM 
    survivalgamesstats AS t 
ORDER BY
    Ranking ;

Both tested in SQLFiddle

Retrieving row ranking in a table

3 Answers3

UPDATE 2014-08-25 16:57 EDT

UPDATE 2014-08-25 17:15 EDT

UPDATE 2014-08-25 17:38 EDT

Give it a Try !!!

Linked