How to rank a record with same weight in MySQL

Question

Suppose I have a data say:

  Name   | Marks   
StudentA | 90
StudentB | 85
StudentC | 85
StudentD | 70

now StudentA will get 1st Rank, StudentB and StudentC will get 2nd Rank and Student D will get 4th Rank.

I know the basic rank computation if there are no duplicate weights, but how to handle if we encounter 2 similar weights as in this case there are two 85 marks which will share rank 2.

What is your current result? What are you doing to get it? What is your desired result? — Patrick Q, Aug 29 '16 at 19:50
I am using a variable and then every time I increment by 1, but there are two 85 in the data which share the same rank so how to handle that? — komal sarvaiya, Aug 29 '16 at 19:53
I want that the rank should be shared if there are equal weights, like here in case of 85, studentB and studentC will share the same rank i.e. 2 — komal sarvaiya, Aug 29 '16 at 19:55
`SELECT name, marks, @curRank := @curRank + 1 AS rank FROM student s, (SELECT @curRank := 0) r ORDER BY marks desc;` — komal sarvaiya, Aug 29 '16 at 20:01
@PatrickQ, It should be 4th rank, I wrote wrong initially, but than I edited. — komal sarvaiya, Aug 29 '16 at 20:03
[this answer](http://stackoverflow.com/a/8913662) seems to do what you want. You could just remove the unneeded columns. — Patrick Q, Aug 29 '16 at 20:09

Andrej · Answer 1 · 2016-08-29T20:12:54.863

1

In php you can implement it in the following way:

$data = [
    [95, 0], [85, 0], [85, 0], [85, 0], [70, 0], [70, 0], [50, 0]
];

$rank = 0;
$previous = null;
$skippedRank = 0;
foreach ($data as &$item) {
    if ($item[0] != $previous) {
        $rank += $skippedRank+1;
        $previous = $item[0];
        $skippedRank = 0;
    } else {
        $skippedRank++;
    }

    $item[1] = $rank;
}

print_r($data);

where $item[0] is weight and $item[1] is rank.

edited Aug 29 '16 at 20:12

answered Aug 29 '16 at 19:59

Andrej

7,474
1
19
21

Thanks for your reply, but I need in MySQL. – komal sarvaiya Aug 29 '16 at 20:04
1

@komalsarvaiya Why did you tag the question with PHP if you weren't interested in it? – Don't Panic Aug 29 '16 at 20:07

score 1 · Answer 2 · edited Aug 29 '16 at 22:53

1

You can use an additional variable to hold the mark of the previous record:

SELECT Name, Marks,
       @rnk := IF(@prevMark = Marks, @rnk,
                  IF(@prevMark := Marks, @rnk + 1, @rnk + 1)) AS rank                
FROM mytable
CROSS JOIN (SELECT @rnk := 0, @prevMark := 0) AS vars
ORDER BY Marks DESC

Demo here

edited Aug 29 '16 at 22:53

Strawberry

33,750
13
40
57

answered Aug 29 '16 at 20:01

Giorgos Betsos

71,379
9
63
98

Giorgos the studentD should hold the 4th rank and not the 3rd rank as the 2nd rank is shared by 2 people. – komal sarvaiya Aug 29 '16 at 20:10

Strawberry · Accepted Answer · 2016-08-29T23:04:57.870

Using Giorgos's fiddle...

SELECT name
     , marks
     , FIND_IN_SET(marks, (SELECT GROUP_CONCAT(marks ORDER BY marks DESC) FROM mytable)) rank
  FROM mytable;

|     Name | Marks | rank |
|----------|-------|------|
| StudentA |    90 |    1 |
| StudentB |    85 |    2 |
| StudentC |    85 |    2 |
| StudentD |    70 |    4 |

http://sqlfiddle.com/#!9/7cc30/6

or

SELECT name, marks, rank
FROM (SELECT name
     , marks
     , @prev := @curr
     , @curr := marks
     , @i:=@i+1 temp
     , @rank := IF(@prev = @curr, @rank, @i) AS rank
  FROM mytable
     , ( SELECT @curr := null, @prev := null, @rank := 0, @i:=0) vars
 ORDER 
    BY marks DESC,name
      ) x
      ORDER 
    BY marks DESC,name

http://sqlfiddle.com/#!9/287e07/9

How to rank a record with same weight in MySQL

3 Answers3

Linked