MySQL, Get users rank MySQL, Get users rank sql sql

MySQL, Get users rank

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

Dense rank:

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

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 scoreFROM users ui,(SELECT @score:=0, @rank:=0) rORDER BY points DESC

 SET @rownum := 0; SELECT rank, score FROM ( SELECT @rownum := @rownum +1 AS rank, `score` ,`user_id` FROM leaderboard  ORDER BY  `score` DESC , `updated_timestamp` ) as result WHERE `user_id`=$user_id LIMIT 1