Please support us by disabling ad blocker for smarttutorials.net ...

Find Rank of player in MySQL table

Posted by & 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.

find rank of the player from mysql table

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’)

find rank of the player from mysql table

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.

find rank of the player from mysql table

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

 

 .

Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts

If you want any of my script need to be customized according to your business requirement,

Please feel free to contact me [at] muni[at]smarttutorials.net

Note: But it will be charged based on your customization requirement

Get Updates, Scripts & Other Useful Resources to your Email

Join 10,000+ Happy Subscribers on feedburner. Click to Subscribe (We don't send spam)
Every Email Subsciber could have access to download 100+ demo scripts & all future scripts.

Get Instant Script Download Access!