mysql 当前用户的排名,MySQL，获取用户排名

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

解决方案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