Top Mysql Points

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

The final exits look

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

Help Plz

Answer 1

You can do this through variables (see examples in other answers) or through case :

 select myTable.*, case when max1.marks is not null then 3 when max2.marks is not null then 2 when max3.marks is not null then 1 else 0 end as score from myTable LEFT JOIN (select marks from myTable order by marks desc limit 1) AS max1 ON myTable.marks=max1.marks LEFT JOIN (select marks from myTable order by marks desc limit 2,1) AS max2 ON myTable.marks=max2.marks LEFT JOIN (select marks from myTable order by marks desc limit 3,1) AS max3 ON myTable.marks=max3.marks; 

demo can be found here .

Answer 2

My assumption is that you first compute the ranking of each character, and then use it in the case argument in the 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’m a little uncomfortable with this method because MySQL does not guarantee the order of evaluation of two expressions with constants. If @marks were set before @rn , this will not work In practice, this does not seem to happen. And it is more efficient, than the equivalent with a correlated subquery.)

Then you can put this in update with 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 script.

Answer 3

 UPDATE myTable t1 INNER JOIN ( SELECT @row: =@row-1 AS RowPoints, Marks FROM ( SELECT Marks FROM myTable GROUP BY Marks ORDER BY Marks DESC LIMIT 3 ) AS TopMarks INNER JOIN (SELECT @row:=4) AS RowInit ) AS AddPoints ON t1.Marks = AddPoints.Marks SET Points = COALESCE(Points, 0) + AddPoints.RowPoints; 

This should work fine. You must also index the "Signs" column.

Answer 4

The easiest way to do this:

 SELECT t.Name Name, t.Marks Marks, (CASE WHEN Marks = (Select max(marks) from mytable) THEN 3 ELSE 0 END+ CASE WHEN Marks = (Select min(marks) from (Select distinct marks from mytable order by marks desc limit 2) a) THEN 2 ELSE 0 END+ CASE WHEN Marks = (Select min(marks) from (Select distinct marks from mytable order by marks desc limit 3) b) THEN 1 ELSE 0 END) AS `Points` FROM mytable t; 

SQL Fiddle