CODEWITHSUNDEEP
phpmysql
BlackMouse
BlackMouse

Reputation: 4552

Get rank of player from mysql query

I found some good answers for this question, but I can't really get them to work.

I wan't to get a players rank from a hiscore table. 

id   name   score

1    John    10
2    Linda   5
3    Emmy    25

I want to pass in a name in the query (Linda) and get her rank (She only have 5 points in the table above), and get her rank (nr 3).

I found a similar question with this answer, but don't understand it: 

SELECT  uo.*, 
    (
    SELECT  COUNT(*)
    FROM    users ui
    WHERE   (ui.points, ui.id) >= (uo.points, uo.id)
    ) AS rank
   FROM    users uo
   WHERE   id = @id

Thanks in advance

Upvotes: 1

Views: 1402

Answers (3)

nikhil500
nikhil500

Reputation: 3450

SELECT (COUNT(*) + 1) AS rank FROM hiscore WHERE score > (SELECT score FROM hiscore WHERE name = 'Linda')

Upvotes: 2

Baaju
Baaju

Reputation: 2010

SET @rownum := 0;
SELECT rank, name, score 
FROM 
    (SELECT @rownum := @rownum + 1 AS rank, name, score 
     FROM players 
     ORDER BY score DESC)

Upvotes: 1

Devart
Devart

Reputation: 121922

Try this query -

SELECT name, score, rank FROM
  (SELECT *, @r:=@r + 1 rank FROM table_name ORDER BY score DESC) t1,
  (SELECT @r:=0) t2
WHERE name = 'Linda'

Upvotes: 1

Related Questions