How to get rank using mysql query

Posted by mahmud ahsan on February 28, 2010 in MySQL, PHP | 15 Comments

Some days ago I was working in a quiz project. Where user will play quiz and for each correct answer they will earn points. One of the task of this quiz application was, to get rank of a particular user. Now I am showing how could I solve this problem using mysql query.

Here is the table structure:

CREATE  TABLE IF NOT EXISTS `quiz_user` (
`uid` BIGINT UNSIGNED NOT NULL ,
`participated` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`correct` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`wrong` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`created` DATETIME NULL ,
`updated` DATETIME NULL ,
PRIMARY KEY (`uid`) )
ENGINE = InnoDB

This table contains user records of quiz game. Here is an examples:

uid	participated	correct	wrong	created	updated
6508097	3	6	1	2010-02-26 06:51:01	2010-02-26 06:51:01
14535811	2	4	2	2010-02-26 06:51:01	2010-02-26 06:51:01

For simplicity here is only 2 users. But It may contain 1 to unlimited users. So to get rank each time you’ve to check what the status of the user, how many correct answers each user solved. For better performance you can do caching. Now I’m showing how a user will get rank from this table.

Long time ago, I saw a solution of a programmer. Look how he did the solution

function getUserRank($userId){
   // no limit they did the query to get all result
   $sql     =  "SELECT * FROM quiz_user ORDER BY correct DESC";
   $result =  mysql_query($sql);
   $rows  =  '';

   $data = array();
   if (!empty($result))
        $rows      =  mysql_num_rows($result);
   else
        $rows      =  '';

    if (!empty($rows)){
        while ($rows = mysql_fetch_assoc($result)){
                $data[]   = $rows;
        }
    }

   // now they did a php loop to get the user rank by user id
   $rank = 1;
   foreach($data as $item){
       if ($item['uid'] == $userId){
           return $rank;
       }
       ++$rank;
   }
   return 1;
}

Just look at the as usual or brute force solution. This is simply a fucky code. To check each user’s rank you first retrieve all records from mysql query then using a loop you check what the user’s rank. So if the table contains 10000 users and suppose 1000 users concurrently playing this game, then just think how much time will take this function and thus this will decrease performance of your application.

So the question is how could you write the optimal solution for this problem. If you make rank using mysql query then it would be more optimal and faster for your application. Here I’m showing how could I solve the problem:

The MySQL query that solved my problem is here:

SET @rownum := 0;
SELECT rank, correct FROM (
                    SELECT @rownum := @rownum + 1 AS rank, correct, uid
                    FROM quiz_user ORDER BY correct DESC
                    ) as result WHERE uid=xxxxxxxx

Look carefully the solution. If you run this query directly in mysql either using phpmyadmin or other mysql admin tools, then you’ll get the user’s ‘xxxxxxxx’ rank in quiz. Let’s explain the query:

SET @rownum := 0;

In mysql you can assign variable and can use later. So at first I declare a variable rownum and asing 0 as value. To declare a variable in mysql you have to use ‘@’ sign before variable name.

Then I used a subquery within query. Look the subquery. This is the query that actually makes the rank and returns result as a table. This is a simple query where I’ve just added “@rownum := @rownum + 1 AS rank” so you assigned rank in each row.

SELECT @rownum := @rownum + 1 AS rank, correct, uid
FROM quiz_user ORDER BY correct DESC

So after this query’s result now I check what the user’s rank

SELECT rank, correct FROM result WHERE uid=xxxxxxxx;

where result is

SELECT @rownum := @rownum + 1 AS rank, correct, uid FROM quiz_user ORDER BY correct DESC;

So the complete query is:

SET @rownum := 0;
SELECT rank, correct FROM (
                    SELECT @rownum := @rownum + 1 AS rank, correct, uid
                    FROM quiz_user ORDER BY correct DESC
                    ) as result WHERE uid=xxxxxxxx

Now look the php solution:

function getUserRank($userId){
        $sql1       = "SET @rownum := 0";

        $sql2       =   "SELECT rank, correct FROM (
                        SELECT @rownum := @rownum + 1 AS rank, correct, uid
                        FROM quiz_user ORDER BY correct DESC
                        ) as result WHERE uid=$uid";

        // here model.php is a class for database connectivity
        include_once "model.php";
        global $dbconfig; //configuration of database that I assigned in config file
        $md = new Model($dbconfig);

        $md->connectDb();
        mysql_query($sql1); /*as mysql_query function can execute one query at a time */
        $result = mysql_query($sql2);
        $rows = '';
        $data = array();
        if (!empty($result))
            $rows      =  mysql_num_rows($result);
        else
            $rows      =  '';

        if (!empty($rows)){
            while ($rows = mysql_fetch_assoc($result)){
                $data[]   = $rows;
            }
        }

        $md->closeDb();

        //rank of the user
        if (empty($data[0]['rank']))
            return 1;
        return $data[0]['rank'];
}

As you make the ranking in mysql, so this is much faster solution than the ordinary solution. By this way I solved the problem and get the user’s rank in my quiz. You can use this technique for any game or application. For more better performance you can use caching. Hope this article will help you for better understand.

If you think this article kicked ass, subscribe to the RSS feed or follow me on Twitter! Share with your friends, or leave a comment below (or better still, do both!)

Comments (15)

Vadim says:

February 28, 2010 at 5:26 am

Thanks for article. Very good application of variables usage in MySQL queries.

Reply
Dave says:

February 28, 2010 at 11:06 am

Excellent! Thank you for sharing this. Looking forward to more.

Reply
alle says:

February 28, 2010 at 4:14 pm

Thanks for sharing. Just a question: how would you sort out if two or more user has the same value for correct answers?They should not have different ranks.

Reply
mahmud ahsan says:

February 28, 2010 at 8:44 pm

@alle, this is the actual code where if both user have same correct answer, then they have the same rank. But for simplicity I didn’t mention that earlier. Here I’ve used IF logic in mysql
```
SET @rank = 0, @prev_val = NULL;

SELECT rank, correct FROM (
SELECT @rank := IF(@prev_val=correct,@rank,@rank+1) AS rank,
@prev_val := correct AS correct, uid
FROM quiz_user ORDER BY correct DESC
)as result WHERE uid=xxxxxxxxxxxx
```
Reply
Nitin says:

March 19, 2010 at 8:28 am

It appears that this query is doing a table scan, is that the case?

It would be helpful if you can explain what you mean by “rank” and why this query is difficult for mysql to execute efficiently.

Reply
Inam says:

March 25, 2010 at 8:18 pm

Its really great
It solves my problem
Thanks

Reply
Fidel Clotet says:

April 7, 2010 at 8:25 pm

I propose the next solution as a better solution…

It is a pure sql solution… What do you think about it?

At first I get (with a sub select sql) the groups of users that have better or equal correct answers than the user I asking for. This is the code for that:
```
	select correct, count(*)
		from quiz_user
		group by correct
		having correct>=(select correct from quiz_user where uid=$uid);
```
If you have which are the groups with more or equal correct answers you will have which is its rank.

Here you have te complete SQL statment:
```
	select count(*) as rankForMyUid
		from (select correct, count(*)
			from quiz_user
			group by correct
			having correct>(select correct from quiz_user where uid=$uid)) as groupsBetterOrEqualThanMyUid;
```
Reply

Fidel Clotet says:

April 8, 2010 at 12:10 am

Sorry but there was a little equal sign in the last statement sent… Here is the final SQL:

select count(*) as rankForMyUid
		from (select correct, count(*)
			from quiz_user
			group by correct
			having correct>=(select correct from quiz_user where uid=$uid)) as groupsBetterOrEqualThanMyUid;

Dharam says:

April 13, 2010 at 6:10 pm

@Fidel Clotet

Dude, don’t confuse people by writing that complex sub queries. I have tried your solution thinking it a better solution and it took 1hr for me to figure it out. Where as the solution given by “mahmud ahsan” is simply awesome and it took 5mins to get it right.

Very good post Ahsan.. Keep it up.

Reply
Fidel Clotet says:

April 13, 2010 at 11:30 pm

@Dharam

I understand your observation y conclusion.

But think about this: the time it takes you to understand depends on what commonly you do…

Commonly you use procedural programming. The SQL is a declarative language.

Take in count that, i.e., using SQL programming gives more opportunities to the DB engine for optimization.

Here you have a little simpler option (without having):
```
select count(*) as rankForMyUid
from (select correct, count(*)
        from quiz_user
        group by correct
    ) as groupsByCorrect
where correct>=(select correct from quiz_user where uid=$uid);
```
Reply
Ajay says:

June 28, 2010 at 5:12 pm

Another solution

SELECT count(DISTINCT correct) FROM quiz_user WHERE correct > (select correct from quiz_user where uid=$uid)

Reply
alenoosh says:

August 22, 2010 at 11:54 am

Hi

I’m writing a user ranking module for a site. This ranking depends on some criterias and it’s possible to set or unset any one of these criterias in order to consider them in calculating the user rank or not. And here’s the way I’ve implemented the ranking calculation :

when I set one or more of the criterias to be considered in ranking , for each user in the system I insert one record for each criteria , for example :

if I have 2 criterias and both are set and consider that I have two users , I’ll have :

Ranking table
————–
username | criteria | to_be_added | score
————————————————–
user1 | criteria1 | 1 | 0
user1 | criteria2 | 1 | 0
user2 | criteria1 | 1 | 0
user2 | criteria2 | 1 | 0

It means I just set the to_be_added field to 1 for all of them and leave the calculation of score for each criteria for each user to the time the user logins so that to prevent doing all these calculations at once , because there are a huge number of users …

But there is one problem , if I want to show for example the best user (based on the highest score) , the result can’t always be true because some users might not logged in at that time and their score might be zero .

As far as you’ve also written a ranking script I’ve decided to get help from you , how do you think I should solve this problem ?

Thanks in advance for your help …

Reply
- mahmud ahsan says:
  
  August 22, 2010 at 6:09 pm
  
  Try to think simple way. Hope you’ll solve it by yourself. Be confident.
  
  Reply
William says:

August 28, 2010 at 5:50 am

Thanks for the sharing, really a great information. It solved my problem too. Thanks for sharing mahmud. Another great solution & sharing, appreciate that.

Reply
Swan says:

September 2, 2010 at 6:38 pm

Thank you for sharing this with the community.

This is indeed a much better solution than computing rating server-side !

Cheers keep up the good work

Reply