2

Possible Duplicate:
Mysql rank function

I have the following table in MySQL:

===================================================================
id  | member|  total_correct_question | time_taken_in_seconds |

===================================================================
1   |  abc  |     2                    |       4              |
2   |  def  |     3                    |       6              |
3   |  ghi  |     3                    |       4              |
4   |  jkl  |     3                    |       5              |
====================================================================

I want to show the rank of each user from mysql query. Can any one suggest me a query for this?

Community
  • 1
  • 1
PHL
  • 29
  • 1
  • 7
  • Welcome to stackoverflow. This is a very common question. Please take a minute to search the archives first before posting. Often a question was asked and answered already - often several times over. – Leigh Dec 11 '12 at 20:41

2 Answers2

1

try this:

  SELECT @i:=@i+1 AS rank,a.*
  FROM
      (SELECT * 
       FROM   Your_table  
       ORDER BY total_correct_question DESC,
                time_taken_in_seconds)a,(SELECT @i:=0) r


SQL Fiddle demo

Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
1

You can create a subquery which produces cartesian product from the original query and the most interesting is that you can increment it by one (which then result to it RANK Number).

SELECT    @rankNo:=@rankNo+1 RankNo,
          a.*
FROM      Table1 a, (SELECT @rankNo:=0) r 
ORDER BY `total_correct_question` DESC,
         `time_taken_in_seconds` ASC

SQLFiddle Demo

John Woo
  • 258,903
  • 69
  • 498
  • 492