mysql query get specific record

Question

Im doing a rankings table of sorts, and need to find a specific rows position from a query.

eg: SELECT name FROM players ORDER BY points DESC

How would I get the position of the record with the name field of 'John', or any other?

So I want the result to give me one number (the rank of 'John')

http://stackoverflow.com/a/2520377/1315604 – jkrcma Jun 21 '12 at 10:00 — jkrcma, Jun 21 '12 at 10:00

score 1 · Answer 1 · answered Jun 21 '12 at 09:59

1

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, name 
  FROM players ORDER BY points DESC

or

SELECT @rn:=@rn+1 AS rank, name
FROM (
  SELECT name 
      FROM players ORDER BY points DESC
) t1, (SELECT @rn:=0) t2;

answered Jun 21 '12 at 09:59

Romil Kumar Jain

This is a variable declaration syntax in mysql. – Romil Kumar Jain Jun 21 '12 at 10:02
thanks, Im wanting to get a specific row though, like I want to find out what rank 'John' is, so it just returns a number. – user1022585 Jun 21 '12 at 10:10

1 Answers1