0

I have a simple MySQL table where I save the score of play. It looks like that:

**Id  -  UserId  -  Score**

The question is: How do I get what's a users rank? I have a users Id

An Example

**Id - UserId - Score**

1 - AAAA - 150

2 - BBBB - 110

3 - BBBB - 120

4 - CCCC - 102

5 - AAAA - 130

In this very case, IdUser CCCC rank would be 3, because he got the 3rd highest score.

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

Thanks!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Marco Caltagirone
  • 1,206
  • 1
  • 14
  • 23

1 Answers1

3

If you have a user id, you want to return the number of users that have the same or greater score.

select count(*) as `rank`
from table t join
     (select score from table t where userId = @USERID) var
     on t.score >= var.score;

If you have duplicate scores, you might really want:

select 1 + count(*) as `rank`
from table t join
     (select score from table t where userId = @USERID) var
     on t.score > var.score;

It is unclear what you want with duplicate users. I might suggest:

select 1 + count(*) as `rank`
from (select userId, max(score) as score
      from table t
      group by userId
     ) t join
     (select max(score) from table t where userId = @USERID) var
     on t.score > var.score;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786