How to get the rank of a row in mysql query

Question

this my database structure

table : players

id  | name      |  score
1   | Bob       |  600
2   | Alex      |  1400
3   | John      |  800
4   | sara      |  2000

I need to select john's row and count what is the john' rank OrderBy score

as you see john is 3rd (800) , sara is 1st (2000), Alex is 2nd (1400) in score ranks

Select @rownum := @rownum + 1 AS rank form players where id=3 OrderBy score

any idea ?

Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — jberryman, Apr 26 '17 at 16:30

Ahmad · Accepted Answer · 2014-11-14T12:35:23.787

You can do it by a subquery and count the players who has score more than the score of a certian id

Select count(*) as rank 
from players 
where score > (select score from players where id=3)

But if you want to have other information beside the rank you can do it by

SELECT ranks . *
FROM (    
   SELECT @rownum := @rownum +1 ‘rank’, p.id, p.score
   FROM players p, (SELECT @rownum :=0)r
   ORDER BY score DESC
) ranks
WHERE id =3

juergen d · Answer 2 · 2017-02-10T18:52:14.990

1

select rank
from
(
  Select id, name, @rownum := @rownum + 1 AS rank 
  from players 
  cross join (select @rownum := 0) r
  Order By score desc
) tmp
where id = 3

edited Feb 10 '17 at 18:52

answered Nov 14 '14 at 11:21

juergen d

201,996
37
293
362

1

"From" clause is mistyped, but this solution works perfect without having to run 2 queries (one for setting up a variable and another to run rank the records). – shimatai Feb 10 '17 at 17:53

score 0 · Answer 3 · answered Nov 14 '14 at 11:18

Might be easier to do a self join, where the joined table score is greater (to get the rows with a higher score) and just do a count:-

SELECT COUNT(*)
FROM players a
INNER JOIN players b
ON a.score >= b.score
WHERE a.id = 3

Question is what to do with equal scores.

How to get the rank of a row in mysql query

3 Answers3

Linked