How to get rank using mysql query?

I was working in a Online Test project. One of the task of this oniline test 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:

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.

I saw a solution of a programmer. Look how he did the solution

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 writting this test, 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:

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.

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

where result is

So the complete query is:

Now look the php solution:

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 application. You can use this technique for any application or game. For more better performance you can use caching. Hope this article will help you for better understand.

Thanks!. If you like this article please don’t forget to share and comment.