Getting player rank from database

I have a RuneScape private server, which stores the player scores in a database. The highscores load the player's scores and put them into a table.

But now comes the harder part I can't fi开发者_开发知识库x:

I want to display the rank of the player. Like: 'Attack level: 44, ranked 12'. So it has to find the rank the user has.

How can I get this to work? I googled for 2 days now, I did not find anything.

I don't know if there's a way to achieve this using the same query.

You could make another query like:

pos = select count(*) from players where attack > 44 + 1

This query would return the number of players ranked above someone. The "plus one" part is to make the rank start at 1 (because the first one won't have anyone ranked above him).

For example, if the table is:

id attack
 0     35
 1     22
 2    121
 3     76

pos(3) = 1 (only player 2 is ranked above) + 1 = 2

You can create a view (probably) that shows every players score. Something along these lines might work.

create view player_scores as 
select player_id, sum(score) 
from scores
group by player_id

That will give you one row per player, with their total score. Having that view, the rank is simple.

select count(*) 
from player_scores
where sum > (select sum from player_scores where player_id = 1)

That query will return the number of players having a higher score than player_id = 1.

Of course, if you know your player's score before you run the query, you can pass that score as a parameter. That will run a lot faster as long as the column is indexed.