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

MySQL, Get users rank


sql mysql rank 

SELECT  uo.*,         (        SELECT  COUNT(*)        FROM    users ui        WHERE   (ui.points, ui.id) >= (uo.points, uo.id)        ) 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

sql mysql rank

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

sql mysql rank 

 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

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last