Mysql – Add ranking to query output

MySQLrank

I'm new at programming with PHP/mySQL and I have got a little problem here.

This is how my code looks like:

SELECT  
 COUNT(*) AS Number , Name 
FROM 
 table 
GROUP BY 
 Name 
ORDER BY 
 Number DESC 
LIMIT 100

Out of this table:

Oliver
Michael
Oliver
Steven
Michael
Oliver
Oliver

… it generates:

Oliver  | 4
Michael | 2
Steven  | 1

My question: How can I add ranking numbers so that it looks like this:

1 | Oliver  | 4
2 | Michael | 2
3 | Steven  | 1

Thanks for your help!

Best Answer

SELECT  state, num_cities, @n := @n+1 AS rank
    FROM  
      ( SELECT  state, count(*) AS num_cities
            FROM  us
            GROUP BY  state
            ORDER BY  num_cities DESC 
      ) AS x
    JOIN  
      ( SELECT  @n:=0 ) AS init

+-------+------------+------+
| state | num_cities | rank |
+-------+------------+------+
| CA    |        447 |    1 |
| FL    |        264 |    2 |
| TX    |        238 |    3 |
| IL    |        221 |    4 |
| NY    |        185 |    5 |
| OH    |        185 |    6 |
| MA    |        180 |    7 |

Related Solutions

MySQL Score Ranking – No Tie, Order by Score Submit Date

Order by Score in descending order, and by SubmitDate in ascending order, then use a variable to give correlative numbers to each row:

mysql> SET @Rank := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *, @Rank := @Rank + 1 AS Rank FROM ranking ORDER BY Score DESC, SubmitDate ASC;
+----+-------+-------+---------------------+------+
| Id | Name  | Score | SubmitDate          | Rank |
+----+-------+-------+---------------------+------+
|  4 | Bash  |   102 | 2014-08-04 21:37:17 |    1 |
|  1 | Ida   |   100 | 2014-08-01 21:37:17 |    2 |
|  2 | Boo   |    99 | 2014-08-02 21:37:17 |    3 |
|  3 | Lala  |    99 | 2014-08-03 21:37:17 |    4 |
|  5 | Assem |    99 | 2014-08-05 21:37:17 |    5 |
+----+-------+-------+---------------------+------+
5 rows in set (0.00 sec)

Mysql – Retrieving row ranking in a table

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.