CODEWITHSUNDEEP
mysqlsqlranking
Marco Caltagirone
Marco Caltagirone

Reputation: 1206

Get the rank of a user in a score table

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!

Upvotes: 0

Views: 840

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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;

Upvotes: 3

Related Questions