MySQL, Get users rank

I have a mysql table like below:

id     name     points
1      john     4635
3      tom      7364
4      bob      234
6      harry    9857

I basically want to get an individual user rank without selecting all of the users. I only want to select a single user by id and get the users rank which is determined by the number of points they have.

For example, get back tom with the rank 2 selecting by the id 3.

Cheers

Eef

Solution 1:

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

Dense rank:

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

Solution 2:

Solution by @Quassnoi will fail in case of ties. Here is the solution that will work in case of ties:

SELECT *,
IF (@score=ui.points, @rank:=@rank, @rank:=@rank+1) rank,
@score:=ui.points score
FROM users ui,
(SELECT @score:=0, @rank:=0) r
ORDER BY points DESC