Points on highest record in Mysql

Question

My table

+------+-------+--------+
| NAME | MARKS | POINTS |
+------+-------+--------+
| S1   |    53 | (null) |
| S2   |    55 | (null) |
| S3   |    56 | (null) |
| S4   |    55 | (null) |
| S5   |    52 | (null) |
| S6   |    51 | (null) |
| S7   |    53 | (null) |
+------+-------+--------+

Refer : http://www.sqlfiddle.com/#!2/5d046/1

I would like to add 3,2,1 points to the highest Marks. Here S3 goes to 3 points, S2,S4 goes to 2 points and S1,S7 goes to 1 points.

Final outputs looks,

+------+-------+--------+
| NAME | MARKS | POINTS |
+------+-------+--------+
| S1   |    53 |   1    |
| S2   |    55 |   2    |
| S3   |    56 |   3    |
| S4   |    55 |   2    |
| S5   |    52 |   0    |
| S6   |    51 |   0    |
| S7   |    53 |   1    |
+------+-------+--------+

Plz help

Answer 1

My suggestion is that you first calculate the ranking of each mark, and then use that in a case statement in an update.

The following query shows one way to calculate the ranking:

select t.*,
       @rn := if(@marks = marks, @rn, @rn + 1) as ranking,
       @marks := marks
from myTable t cross join
     (select @rn := 0, @marks := -1) const
order by t.marks desc;

(As a note: I am a bit uncomfortable with this method, because MySQL does not guarantee the order of evaluation of the two expressions with constants. If @marks were set before @rn, then it wouldn't work. In practice, that does not seem to happen. And, this is more efficient that the equivalent with a correlated subquery.)

You can then put this into an update using join:

update myTable join
       (select t.*,
               @rn := if(@marks = marks, @rn, @rn + 1) as ranking,
               @marks := marks
        from myTable t cross join
             (select @rn := 0, @marks := -1) const
        order by t.marks desc
       ) mr
       on myTable.Name = mr.Name
    set myTable.Points := (case when mr.ranking = 1 then 3
                                when mr.ranking = 2 then 2
                                when mr.ranking = 3 then 1
                                else 0
                           end);

This has been tested on your SQL Fiddle.