0

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.

Improve this question
2
  • Question for Clarification: You want the output to be HEX(UUID) as UUID, Username, Rating followed by all ranks that share that same rating (like 1600 (1,2), 1536 (3), 1234 (4), 455 (5)) or do you just want the rank of jkz101022 (455) ?
    – RolandoMySQLDBA
    Aug 25, 2014 at 17:22
  • I just want the rank of jkz101022 (455). So a query would return that person's username, uuid, rating, and ranking.
    – Davif
    Aug 25, 2014 at 17:27

3 Answers 3

Reset to default
2

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.

Improve this answer
5
  • This is just a copy and paste from the answer in the linked question.
    – Davif
    Aug 25, 2014 at 19:54
  • @Davif Please notice that I cut and pasted MY ANSWER from that post. Also notice that I embedded the Username into it along with the JOIN back to the mytable to retrieve the one row. Did you try the query out ???
    – RolandoMySQLDBA
    Aug 25, 2014 at 19:56
  • See fiddle: sqlfiddle.com/#!2/298d5/3. I needed to change score -> Rating. Feel free to update it.
    – Davif
    Aug 25, 2014 at 20:16
  • My full table schema: this works in fiddle but not on my machine sqlfiddle.com/#!9/c545b/2
    – Davif
    Aug 25, 2014 at 20:33
  • For my query, MySQL Client produces the correct rank. SQLFiddle does not. SQLFiddle seems to increment my user-defined variables five times before using them.
    – RolandoMySQLDBA
    Aug 25, 2014 at 21:18
1

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 :-)

Improve this answer
5
  • Using mysql 5.6.12 I ran this query on the table and it return the ranking of 0 for each row.
    – Davif
    Aug 25, 2014 at 19:51
  • I should note these aren't the only columns in the table, there are other columns if it matters. Could it be my sql version? The fiddle works as intended sqlfiddle.com/#!2/298d5/1
    – Davif
    Aug 25, 2014 at 20:02
  • With respect to your 5.6.12 instance returning 0 for each row, your SQL Fiddle using 5.5.32 works and my 5.6.19 also works - so one would think that it should work for all versions in between. The only thing that I can think of is some sort of problem with the instance itself - the install, something corrupted then or since? Post SHOW CREATE TABLE scores for both of your instances - it's hardly a MyISAM/InnoDB issue - my engine is InnoDB. As for other columns, if all you're doing is selecting them along with UUID, Usename and Rating, then that will have no effect.
    – Vérace
    Aug 25, 2014 at 20:33
  • My full table schema sqlfiddle.com/#!9/c545b/2 Once again I'm torn because I just tested it on the fiddle and it worked by not on my localhost or staging server.
    – Davif
    Aug 25, 2014 at 20:34
  • Your second SQL Fiddle works like a charm on my machine also. I also ran the SQL Fiddle with 5.5.32. This points to some systemic problem with your instances of MySQL. What I would recommend is that you do a reinstall from scratch. Take a dump of your database and reload after the reinstall. I might even be tempted to wipe the machines entirely and reinstall the OS as well.
    – Vérace
    Aug 25, 2014 at 22:15
1

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

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.