How to get rank using mysql query

mysql 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.

About mahmud ahsan

Founder And Lead Programmer at iThinkdiff.net

, , , , ,

60 Responses to How to get rank using mysql query

  1. Vadim February 28, 2010 at 5:26 am #

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

  2. Dave February 28, 2010 at 11:06 am #

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

  3. alle 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.

  4. mahmud ahsan 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
    
    • mohammad June 28, 2011 at 9:13 pm #

      Thanks for sharing this Code You Are the best

    • Totoy Lubacon April 22, 2013 at 2:58 pm #

      Please help I tried your code but the result is all rank no. 1 below is my code.

      $sqlGetRank = @mysql_query("SELECT STUDENTNUM,GWAGRADE FROM gwa");
         while($rs = mysql_fetch_array($sqlGetRank))
         {
           $stdID = $rs['STUDENTNUM'];
      	 $rank = getUserRank($stdID);
           echo $stdID ." " .$rs['GWAGRADE']." " .$rank .'';
         
         
         }
      
      
      function getUserRank($userId){
              $sql1       = "SET @rank = 0, @prev_val = NULL;";
              $sql2       =   "SELECT rank,GWAGRADE FROM (
                              SELECT @rank := IF(@prev_val=GWAGRADE,@rank,@rank+1) AS rank,
      						@prev_val := GWAGRADE AS GWAGRADE,STUDENTNUM 
                              FROM gwa ORDER BY GWAGRADE DESC
                              ) as result WHERE STUDENTNUM=$userID";
       
              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;
                  }
              }
       
         
       
              //rank of the user
              if (empty($data[0]['rank']))
                  return 1;
              return $data[0]['rank'];
      }
      
    • Mikevl August 14, 2013 at 12:41 pm #

      have been hunting for this for months however I am trying to do this with a sum of score points and can’t see how to get the previous rows score to do the if statement

      any help would be great thanks

    • SAMUEL DERVIS November 22, 2013 at 12:30 pm #

      @mahmud
      thank you very much for helping me out.
      i tried your code and i got this results
      RANK TOTAL
      1 79
      2 69
      2 69
      2 69
      3 59
      4 30

      I would wish that if there was a tie in rank 2, which are three in this case, the next rank should be 5 and not three. i hope you can understand what i mean.
      please help, i will appreciate

  5. Nitin 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.

  6. Inam March 25, 2010 at 8:18 pm #

    Its really great :)
    It solves my problem :)
    Thanks

  7. Fidel Clotet 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;
    
    • swadesh October 9, 2012 at 4:46 pm #

      <?php
      if(isset($_POST['s1']))
      {
      $u1=$_POST['e1'];
      $u2=$_POST['e2'];
      include ('db.php');

      $sql1 = "SET @rank := 0";
      mysql_query($sql1);
      $sql2="SELECT @rank:=@rank+1 AS ran,price,aid
      FROM myfavorite
      ORDER BY price DESC";
      $result1=mysql_query($sql2);
      //$result=mysql_query("select
      //* from myfavorite ORDER BY price DESC");

      echo "

      AID
      MOBILE
      PRICE
      RANK

      “;

      while($row = mysql_fetch_array($result1))
      {
      echo “”;
      echo “” . $row['aid'] . “”;

      echo “” . $row['mobile'] . “”;
      echo “” . $row['price'] . “”;
      echo “” . $row['ran'] . “”;

      echo “”;
      }
      echo “”;
      }
      //$row3=mysql_query(“select position,securemark
      //rank() ORDER BY securemark
      //from student1″);
      //while($row1 = mysql_fetch_array($row3))
      //{
      // $a1= $row1['position'] ;
      //}

      ?>

  8. Fidel Clotet 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;
    
  9. Dharam 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.

    • Uatek October 28, 2011 at 3:00 am #

      @Dharam

      Well, the Fidel’s query was in fact the one that showed me how to solve my problem. So don’t criticize who only wants, and in fact did, help with his own point of view.

      Thanks Fidel and Ahsan.

  10. Fidel Clotet 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);
    
  11. Ajay 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)

    • Ahmad February 15, 2011 at 7:58 am #

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

      thank u, sub query may return only one rows ! max(correct)

  12. alenoosh 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 …

    • mahmud ahsan August 22, 2010 at 6:09 pm #

      Try to think simple way. Hope you’ll solve it by yourself. Be confident. :)

  13. William 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.

  14. Swan 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

  15. Dom October 25, 2010 at 5:21 am #

    How would you group by uid? Assuming you insert a new row if the user does the same quiz, it’ll throw off the ranking

  16. Gamer November 25, 2010 at 6:31 pm #

    Thanks a lot Mr. mahmud ahsan , your code is working fine for me….

  17. Jack November 27, 2010 at 10:48 pm #

    Is this example using mySQL ver 5.1?
    I have a similar ranking query that has worked fine in ver 5.0, but doesn’t sort correctly in ver 5.1. It seems to calculate “@rownum := @rownum + 1″ before it applies the ordering.

  18. Rolex Jewelry January 3, 2011 at 11:49 am #

    This is very helpful website..

    This is very helpful and informative blog….

    Thanks for sharing information..

    nice one i really like it..

  19. bashir January 27, 2011 at 12:22 pm #

    Your topics are best, Thanks for this post here.

    I have a problem that :
    Can i use MySQL database in my local pc to connect with the Facebook application ?
    I have used but error has come.
    my connection code is :

    $hostname = “localhost”;
    $database = “bdnamel”;
    $user = “username”;
    $pass = “”;

    $db = mysql_connect($hostname, $user,$pass);
    mysql_select_db($database,$db);

    Please suggest me….

    The error is the following:

    PHP Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘root’@'localhost’ (using password: NO) in

    PHP Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in

    PHP Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in

    PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

    What’s the problem for which above error ?

    • mahmud ahsan January 27, 2011 at 2:14 pm #

      If you want to use your pc’s web server for facebook app, you need to have a dedicated IP that points to your pc. Mysql is not related with it, because it will control from the web server, so far if you’ve dedicated IP you can use both. By the way your error says, invalid password and username so please check it.

      • bashir January 27, 2011 at 2:38 pm #

        Thanks for reply.
        Actually I have solved my problem before some moments of your reply.

  20. bashir January 27, 2011 at 6:50 pm #

    I have a Quiz Application in facebook platform and when I click the submit button after correct the question then form data would not submit in the result page Internet Explorer but in Firefox this is correct.
    Also if I try secondly after first time in IE the result of a form data would submit and result is come.
    I was spent my huge time to correct this but i can’t do it.
    What is the problem and which will be suggesion to me to solve this problem.
    Please suggest me…

  21. Ahmad February 15, 2011 at 8:06 am #

    Solution:

    SELECT count( DISTINCT username )
    FROM ranking_table
    WHERE score > (
    SELECT max( score )
    FROM ranking_table
    WHERE `username` = “tony”
    GROUP BY `username` )

  22. Survey Tool June 1, 2011 at 5:59 pm #

    Blogs are so interactive where we get lots of informative on any topics nice job keep it up !!

  23. Muhammad Junaid Akhter Dar June 24, 2011 at 7:29 pm #

    I am trying to get the ranking of a users friend from the table.. need to extract only the users who are friends of the current user but the rank must be their actual rank in the entire ranking system (I call it world)

    WHERE u.user_id IN (“.$friendIds.”)
    GROUP BY u.user_id
    ORDER BY account_balance DESC
    LIMIT 0,25

    and

    WHERE u.user_id IN (“.$friendIds.”)
    GROUP BY u.user_id
    ORDER BY earning_sum DESC
    LIMIT 0,25

    giving the friendIds limits the extraction to only friends but it messed the rank also as u see I have a limit of 25, if I continue with this limit and remove friendIds clause i.e. hit world ranking and try to extract friends from php then friends having rank over 25 are not shown.over

    any help in this regard would be highly appreciated.

    Regards

  24. saad October 1, 2011 at 3:10 am #

    Warning: Missing argument 1 for getUserRank(), called in /pool.php on line 180 and defined in /pool.php on line 154
    1

    i am having error i just make table like u and use this code

  25. saad October 1, 2011 at 3:11 am #

    code don’t show in my last post here is code

  26. Clutch December 29, 2011 at 3:29 am #

    Hey, thank you so much for this! It’s very helpful! I’m only having one issue, that I’m sure is a simple one. Here’s the code I’m using-

    $rank = mysql_query(“SET @rownum := 0;
    SELECT rank, power FROM (
    SELECT @rownum := @rownum + 1 AS rank, power, id
    FROM players ORDER BY power DESC
    ) as result WHERE id=’$id’”);

    And the code works great when I put it in PHPMyAdmin, the only trouble I’m having is printing out the result in PHP on my webpage. I tried echoing $rank and doing $rankfind = mysql_fetch_array($rank); echo $rankfind['rank'];

    but that didn’t work either. Quick fix anyone?

  27. Mukesh January 18, 2012 at 10:17 pm #

    Thanks a lot Mahmud.

    A variation for calculating the percentile along with rank –

    set @totalStudents = 0;
    select count(*) into @totalStudents from marksheet;
    SELECT id, score, @curRank := IF(@prevVal=score, @curRank, @studentNumber) AS rank,
    @percentile := IF(@prevVal=score, @percentile, (@totalStudents – @studentNumber + 1)/(@totalStudents)*100),
    @studentNumber := @studentNumber + 1 as studentNumber,
    @prevVal:=x
    FROM marksheet, (
    SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1, @percentile:=100
    ) r
    ORDER BY score DESC

  28. Ted S March 15, 2012 at 6:01 am #

    Great writeup… Thanks for providing the code and walking through it!

  29. manan March 27, 2012 at 10:27 am #

    i have table

    create table polls(
    id int not null auto_increment primary key,
    title varchar(400) NOT NULL,
    description varchar(500) NOT NULL,
    activation_time DATE NOT NULL,
    expire_time DATE NOT NULL,
    is_published boolean NOT NULL Default false);

    when i update
    update rating set is_published = true where id = ?
    that time is_published field updeted bat activation_time also update and gate the system time and chang the activation_time time
    i want only update is_published field what can i do plz help me my database is mysql 5.5

  30. manan March 27, 2012 at 10:29 am #

    i have table

    create table polls(
    id int not null auto_increment primary key,
    title varchar(400) NOT NULL,
    description varchar(500) NOT NULL,
    activation_time DATE NOT NULL,
    expire_time DATE NOT NULL,
    is_published boolean NOT NULL Default false);

    when i update
    update polls set is_published = true where id = ?
    that time is_published field updeted bat activation_time also update and gate the system time and chang the activation_time time
    i want only update is_published field what can i do plz help me my database is mysql 5.5

  31. Tobias March 29, 2012 at 7:36 pm #

    Saved my day buddy!

  32. Agile Development April 25, 2012 at 12:36 am #

    Now a days facebook web applications developing are highly demand-able.

  33. Bala April 30, 2012 at 2:38 pm #

    What is the best way to retrieve some of the IDs from the INSERT statement without using an SQL query statement again?

    Here is the closest one that I could see, but can’t get it to work:
    $sqlQuery = “INSERT INTO orders (item,count) VALUES (‘beef’, ’1′) RETURNING receiptNumber INTO @invoiceNum”;

    The error is straightforward, but don’t know how to solve it.
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘RETURNING receiptNumber INTO @invoiceNum’ at line 1

    Any pointers would be appreciated. FYI: This is done through Php / MySQL.

  34. Akshay May 1, 2012 at 5:46 pm #

    Thanks Mr. Ahsan…I am become fan of you..Really great.

  35. Shay June 4, 2012 at 9:08 pm #

    You are the man! I have looked at several ways to implement this and by far this has been the easiest solution yet! I am not an SQL or PHP Guy but this worked for me very quickly…

    Woot! Woot!

  36. Alan Henager June 5, 2012 at 1:05 am #

    Yet another solution, standard ANSI SQL with no sub-selects:

    SELECT COALESCE(COUNT(qu2.uid), 0) + 1 as rank, MAX(qu.correct) as correct
    FROM quiz_user qu
    LEFT JOIN quiz_user qu2 ON qu.correct < qu2.correct
    WHERE qu.uid = xxxxxxxx

  37. Echt Einfach TV Mathe June 15, 2012 at 2:57 pm #

    Using mysql to substitute 25 lines of php code, similar to your example.

    Many people don’t know about the power of mysql, probably due to its not-so-easy syntax.
    But as soon as you know, it becomes a powerful tool (esp. regarding performance)!

  38. Rajeev July 17, 2012 at 4:09 pm #

    Have any of you guys bothered to see the explain plan of your queries? It is amazing how to get rank every one of the solutions here is doing a full table scan. Also, it is equally amazing to see your PHP code loading full tables without any predicate. That just tells me that none of you have seen tables with million of rows!

    There is no sane way to rank rows in MySQL. You are doing an expensive operation that may work for small tables but will never work for huge tables. Consider working with a small set of data using keys.

    • mahmud ahsan July 19, 2012 at 2:59 pm #

      Yes sir, we didn’t work on big table, so would you please write a post how to work on big table for this problem, so we can learn about that.

      • ZIA January 15, 2013 at 1:22 pm #

        I want to sum(numbers) instead correct colum, because I have a marksheet and I want to sum numbers in this query instead correct colum with I have some users at same rank. Please help.

    • schodemeiss August 7, 2012 at 6:55 pm #

      I too am working on a large dataset (about 3 million). We’re trying to get a rank for a specific UserID, along with 9 results above it.

      This in SQL, without “filesort” or a full table scan, is incredibly tough. If anyone has managed to come up with a solution, be my guest to post it here!

      • Sebastien September 17, 2012 at 8:24 pm #

        @schodemeiss I did a test with 500k rows with a 40% improvement by limiting the inner query to an approximate limit and adjust the offset

        You could, for example, store last computed rank for a user and use it +/- 10000 position to limit your query.

        Example with last_rank at 100000:

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

  39. bb October 5, 2012 at 6:47 am #

    Saved my day buddy!

  40. Anniyan December 11, 2012 at 6:45 pm #

    You came to my rescue. :) I’m not that experienced with SQL and thought of the brute force approach first for assigning same rank to players having the same score. Modified your query to fit my game and it works like a charm. :)

    Cheers!!

  41. higames December 14, 2012 at 1:19 pm #

    Helped me in assigning ranks for on my game site.Thanks

  42. Zia January 14, 2013 at 2:17 am #

    I want to sum(numbers) instead correct colum, because I have a marksheet and I want to sum numbers in this query instead correct colum with I have some users at same rank. Please help.

  43. Keukentafel January 16, 2013 at 8:28 pm #

    Helped me a lot in developing my next facebook-app.
    Tnx!

  44. Why February 23, 2013 at 3:56 am #

    There is quite a bit of spam here

  45. AOG April 12, 2013 at 3:26 am #

    I NEED HELP WITH MYSQL RANK.

    PLEASE WHENEVER THE SUBJECT SCORE IS ABOVE 99 (e.g 100), THE RANKING GETS IT WRONG. IF IT WERE TO WORK PROPERLY, THE RANKING OF 100 SCORE SHOULD BE 1ST , BUT WITH THE BELOW SQL IT RETURNS LAST POSITION.
    NOTE: ALL SCORES BETWEEN 1-99 WORKED PERFECTLY (PERFECT RANKING).
    KINDLY HELP OUT: MY EMAIL ADDRESS IS DELUXE_K@YAHOO.COM

    SET @rank =0;
    SELECT  `rank` ,  `rid` ,  `agric` 
    FROM (
    
    SELECT rid, reg_no, agric, @prev := @curr , @curr := agric, @rank := IF( @prev = @curr , @rank , @rank +1 ) AS rank
    FROM result, (
    
    SELECT @curr := NULL , @prev := NULL , @rank :=0
    )sel1
    WHERE  `class` =  'JSS3' &amp;&amp;  `session` =  '2012/2013' &amp;&amp;  `term` =  '1st'
    ORDER BY agric DESC
    ) AS r1
    WHERE rid =  '10';
    
  46. Marco February 6, 2014 at 6:21 am #

    Thank you very much, this works very well!

Leave a Reply