How best to get someone's 'rank' from a scores table with php and mysql without looping

Question

My table will hold scores and initials.

But the table wont be ordered.

I can get the total row count easy enough and I know I can get all of them and Order By and then loop through them and get the rank THAT way...

But is there a better way? Could this maybe be done with the SQL statement?

I'm not TOO concerned about performance so if the SQL statement is some crazy thing, then Ill just loop.

Sorry - Table has id as primary key, a string to verify unique app install, a column for initials and a column for score.

When someone clicks GET RANK... I want to be able to tell them that their score is 100 out of 1000 players.

Answer 1

SELECT s1.initials, (
  SELECT COUNT(*)
  FROM scores AS s2
  WHERE s2.score > s1.score
)+1 AS rank
FROM scores AS s1

Answer 2

Do you have a primary key for the table or are you fetching data by the initials or are you just fetching all the data and looping through it to get the single record? How many results are you trying to fetch?

If you only want to fetch one record, as the title suggests, you would query the database using a WHERE conditional:

SELECT score FROM table WHERE user_id = '1';

Answer 3

See this answer: https://stackoverflow.com/a/8684441/125816

In short, you can do something like this:

SELECT id, (@next_rank := IF(@score <> score, 1, 0)) nr, 
           (@score := score) score, (@r := IF(@next_rank = 1, @r + 1, @r)) rank 
FROM rank, (SELECT @r := 0) dummy1
ORDER BY score DESC;

And it will produce a result like this:

+------+----+-------+------+
| id   | nr | score | rank |
+------+----+-------+------+
|    2 |  1 |    23 |    1 |
|    4 |  1 |    17 |    2 |
|    1 |  0 |    17 |    2 |
|    5 |  1 |    10 |    3 |
|    3 |  1 |     2 |    4 |
+------+----+-------+------+

Note that users with equal scores have equal ranks. :-)