Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score

So far so good. The question is: How do I get what's a users rank? For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.

An Example

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

In this very case, Assem's rank would be 3, because he got the 3rd highest score.

The query should return one row, which contains (only) the required Rank.

share|improve this question
2  
Your text/sample data does not clarify why type of ranking you want. Based on your accepted answer we assume you wanted Ordinal Ranking. See en.wikipedia.org/wiki/Ranking –  Leigh Riffel Feb 22 '12 at 22:54
    
@LeighRiffel Yes, exactly. –  Michael Feb 22 '12 at 22:58
add comment

7 Answers

up vote 8 down vote accepted
SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

gives this list:

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'

Gives this result:

id name score rank
5 Assem 99 3
share|improve this answer
    
How will this query behave if we have thousands (or millions) of rows in the table? –  ypercube Feb 23 '12 at 7:22
    
You'll have one scan to get the score list, and another scan or seek to do something useful with it. An index on the Score column would help performance on large tables. –  cairnz Feb 23 '12 at 8:02
    
The correlated (SELECT GROUP_CONCAT(score) FROM TheWholeTable) is not the best way. And it may have a problem with the size of the row created. –  ypercube Feb 23 '12 at 8:04
    
This will fails in case of ties. –  Arvind07 May 3 '13 at 11:14
add comment

When multiple entries have the same score, the next rank should not be consecutive. The next rank should be incremented by number of scores that share the same rank.

To display scores like that requires to rank variables

  • rank variable to display
  • rank variable to calculate

Here is a more stable version of ranking with ties:

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

Let's try this out with sample data. First Here is the sample data:

use test
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(
    id int not null auto_increment,
    score int not null,
    primary key (id),
    key score (score)
);
INSERT INTO scores (score) VALUES
(50),(40),(75),(80),(55),
(40),(30),(80),(70),(45),
(40),(30),(65),(70),(45),
(55),(45),(83),(85),(60);

Let's load the sample data

mysql> DROP TABLE IF EXISTS scores;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE scores
    -> (
    ->     id int not null auto_increment,
    ->     score int not null,
    ->     primary key (id),
    ->     key score (score)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO scores (score) VALUES
    -> (50),(40),(75),(80),(55),
    -> (40),(30),(80),(70),(45),
    -> (40),(30),(65),(70),(45),
    -> (55),(45),(83),(85),(60);
Query OK, 20 rows affected (0.04 sec)
Records: 20  Duplicates: 0  Warnings: 0

Next, let initialize the user variables:

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Now, here is the output of the query:

mysql> SELECT score,ID,rank FROM
    -> (
    ->     SELECT AA.*,BB.ID,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    ->     (@curscore:=score) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (SELECT COUNT(1) scorecount,score
    ->         FROM scores GROUP BY score
    ->     ) AAA
    ->     ORDER BY score DESC
    -> ) AA LEFT JOIN scores BB USING (score)) A;
+-------+------+------+
| score | ID   | rank |
+-------+------+------+
|    85 |   19 |    1 |
|    83 |   18 |    2 |
|    80 |    4 |    3 |
|    80 |    8 |    3 |
|    75 |    3 |    5 |
|    70 |    9 |    6 |
|    70 |   14 |    6 |
|    65 |   13 |    8 |
|    60 |   20 |    9 |
|    55 |    5 |   10 |
|    55 |   16 |   10 |
|    50 |    1 |   12 |
|    45 |   10 |   13 |
|    45 |   15 |   13 |
|    45 |   17 |   13 |
|    40 |    2 |   16 |
|    40 |    6 |   16 |
|    40 |   11 |   16 |
|    30 |    7 |   19 |
|    30 |   12 |   19 |
+-------+------+------+
20 rows in set (0.18 sec)

Please note how multiple IDs that share the same score have the same rank. Also note that rank is not consecutive.

Give it a Try !!!

share|improve this answer
add comment

One option would be to use USER variables:

SET @i=0;
SELECT id, name, score, @i:=@i+1 AS rank 
 FROM ranking 
 ORDER BY score DESC;
share|improve this answer
add comment
SELECT id, Name, 1+(SELECT count(*) from table_name a WHERE a.Score < b.Score) as RNK, Score
FROM table_name b;
share|improve this answer
    
This is the best solution for me. Fast and easy to implement. –  videador Feb 23 at 22:51
add comment

Here's the best answer:

SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.score > b.score ) AS rank FROM
highscores b WHERE Name = 'Assem' ORDER BY rank LIMIT 1 ;

This query will return:

3

share|improve this answer
add comment

This solution works in case of ties:

SELECT *,
IF (@score=s.Score, @rank:=@rank, @rank:=@rank+1) rank,
@score:=s.Score score
FROM scores s,
(SELECT @score:=0, @rank:=0) r
ORDER BY points DESC
share|improve this answer
1  
Other solutions don't fail. They give a different ranking, not caring about ties. Your answer gives the DENSE_RANK(). There is also the RANK() but the OP has not specified what exactly he wants. Only in a comment, he mentions he wants Ordinal Ranking which is ROW_NUMBER() in other dbms and what the other answers give. –  ypercube May 3 '13 at 11:31
    
@ypercube Its different perspective then. –  Arvind07 May 3 '13 at 11:36
add comment

I have this, which gives the same results as the one with variables. It works with ties and it may be faster:

SELECT COUNT(*)+1 as rank
FROM 
(SELECT score FROM scores ORDER BY score) AS sc
WHERE score <
(SELECT score FROM scores WHERE Name="Assem")

I didn't test it, but I'm using one that works perfect, which I adapted to this with the variables you were using here.

share|improve this answer
2  
The OP needed four (4) columns, you return one. And this is only one problem... –  dezso Jul 9 '13 at 13:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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