CODEWITHSUNDEEP
mysql
ash
ash

Reputation: 1232

MySQL - How to do an Oracle RANK() OVER(ORDER BY score DESC)

I'm familiar with Oracle and would normally do a RANK() (ORDER BY score DESC) AS rank to get a rank like this:

 Score | Rank
--------------
   100 |    1
   100 |    1
    99 |    3
    98 |    4
    98 |    4
    98 |    4
    97 |    7

I haven't got the foggiest how to do this in MySQL - after looking on here, I've found this:

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT score
     , CASE
       WHEN @prev_value = score 
          THEN @rank_count
       WHEN @prev_value := score 
          THEN @rank_count := @rank_count + 1
       END AS rank
  FROM ...

but this returns it as:

 Score | Rank
--------------
   100 |    1
   100 |    1
    99 |    2
    98 |    3
    98 |    4
    98 |    4
    97 |    5

Which isn't quite what I wanted.

Upvotes: 1

Views: 413

Answers (2)

Strawberry
Strawberry

Reputation: 33935

SELECT score
     , FIND_IN_SET(score,(SELECT GROUP_CONCAT(score ORDER BY score DESC) FROM my_scores))
  FROM my_scores;

or, if you want to get closer to the speed of Barmar's query...

SELECT score
     , FIND_IN_SET(score, @vals) rank
  FROM my_scores
  JOIN (SELECT @vals := GROUP_CONCAT(score ORDER BY score DESC) FROM my_scores) x;

Of course GROUP_CONCAT has certain limitations!

Upvotes: 1

Barmar
Barmar

Reputation: 780899

Add another variable:

SET @prev_value = NULL;
SET @rank_count = 0;
SET @rank_increasing = 0;
SELECT score
     , @rank_increasing := @rank__increasing + 1 AS row_num
     , CASE
       WHEN @prev_value = score 
          THEN @rank_count
       WHEN @prev_value := score 
          THEN @rank_count := @rank_increasing
       END AS rank
  FROM ...

Upvotes: 2

Related Questions