0

I have a table with highscores. When I read them I order them by score DESC.

scores
id  name  score

i.e.

SELECT name, score FROM scores ORDER BY score DESC

Now I would like to know the rank of a person. I am trying to find a way to combine this without having to loop through all the highscores. This is what I thought of, but I know this will not work. Any ideas?

SELECT COUNT(id), name, score FROM scores WHERE name = ? ORDER BY score DESC

Should I use WHERE?

Z0q
  • 1,689
  • 3
  • 28
  • 57
  • When you say rank, do you mean their index in the resultset? – 101010 Nov 23 '15 at 22:35
  • What do you mean the rank of a person? – odannyc Nov 23 '15 at 22:35
  • I mean the counted number. For example: I order them by score DESC. Then I start counting the rows to get their rank: 1, 2, 3, 4. – Z0q Nov 23 '15 at 22:36
  • Sounds like you want something like this: http://stackoverflow.com/questions/1895110/row-number-in-mysql – 101010 Nov 23 '15 at 22:36
  • I need to know the row number of 1 record (e.g. WHERE name = ?) – Z0q Nov 23 '15 at 22:49
  • I found the exact answer to my question here: http://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table – Z0q Nov 23 '15 at 22:54

1 Answers1

2

You could count everyone with a higher score in a subquery:

select coalesce((select count(1) from scores b where b.score > a.score),0) + 1 Rank
, Name
, Score
from Scores a
where name = 'Sarah'

SQL Fiddle: http://sqlfiddle.com/#!9/ff0133/3

JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(order by score desc) rank , score from scores order by score desc LIMIT 0, 30' at line 2 – Z0q Nov 23 '15 at 22:39
  • Apologies; I'd answered for `MS SQL`; for `MySQL` please see link in updated answer. – JohnLBevan Nov 23 '15 at 22:41
  • This is not the answer to my question. I need to know the rank of one person (e.g. WHERE name = ?) – Z0q Nov 23 '15 at 22:48
  • Apologies; not with it today; corrected again. – JohnLBevan Nov 23 '15 at 22:56
  • 1
    Thank you for your answer. This solution is better than the one I used previously! – Z0q Dec 18 '15 at 11:24