Find Rank of player in MySQL table

Posted 1:00 pm by muni & filed under MYSQL.

Mostly in games we have a requirement like to find rank’s of the player in the last week or month. In MySQL there is no default MySQL function to find out rank’s of the player from the player table.

To find rank of the player we need dummy column in the MySQL query which keeps rank of the player based on the score. In the below query rankOfThePlayer is dummy column to keep rank of the player.

Here is the sample player table with data.

Here is the query to find rank of the player from the table.

SET @rownum := 0;

SELECT rankOfThePlayer FROM ( SELECT @rownum := @rownum + 1 AS rankOfThePlayer,id FROM player_information where played_on between DATE_SUB(now(),interval 7 day) and now() ORDER BY score DESC) as result WHERE id="+player_id+"

Result from above query: (which is rank of player one    ‘ id=1’)

I used nested queries to fetch the result. The inner query which sorts the players based on the score of player in descending  order.

SET @rownum := 0;

SELECT @rownum := @rownum + 1 AS rankOfThePlayer,id FROM player_information where played_on between DATE_SUB(now(),interval 7 day) and now() ORDER BY score DESC

The above query fetches the details of the player those who played in last week as well sorts the player based score in descending order.

The outer query gets rank of the player id one using where condition where id=1

SET @rownum := 0;

SELECT rankOfThePlayer FROM ( SELECT @rownum := @rownum + 1 AS rankOfThePlayer,id FROM player_information where played_on between DATE_SUB(now(),interval 7 day) and now() ORDER BY score DESC) as result WHERE id=1

 .