Add specific points on highest marks

Question

I would like to add points on highest marks. my table is

I am trying to give points on Highest 3 marks of a Student. 1st highest Marks goes to 5 points, 2nd highest Marks goes to 3 Marks and 3rd highest goes to 1 marks

I am using this code for selecting highest Marks,

select t1.ID, t1.Name, t1.Section, t1.Marks from myTable t1 join 
(select Section, substring_index(group_concat
 (distinct Marks order by Marks desc), ',', 3) as Marks3  
 from myTable group by Section ) tsum  on t1.Section = tsum.Section 
  and find_in_set(t1.Marks, tsum.Marks3) > 0 ORDER BY Section, Marks DESC, ID Desc

I would like to add 5 points for 1st higest values, 3 points for 2nd highest and 1 points for 3rd highest Marks. Duplicate Marks may occur for students.

Please refer http://www.sqlfiddle.com/#!2/dca0c/1

So my final output

Please help me..

Answer 1

UPDATED You can do it like this

SELECT id, name, section, marks,
       CASE rank WHEN 1 THEN 5
                 WHEN 2 THEN 3
                 WHEN 3 THEN 1
                 ELSE 0
       END points
  FROM
(
  SELECT s.*, @n := IF(@s = section, IF(@m = marks, @n, @n + 1), 1) rank, @m := marks, @s := section
    FROM students s, (SELECT @n := 0) i
   ORDER BY section, marks DESC
) q
HAVING points > 0

Output:

| ID | NAME | SECTION | MARKS | POINTS |
----------------------------------------
|  1 |   S1 |  class1 |    55 |      5 |
|  7 |   S7 |  class1 |    32 |      3 |
|  3 |   S3 |  class1 |    25 |      1 |
| 10 |  S10 |  class2 |    78 |      5 |
| 14 |  S14 |  class2 |    78 |      5 |
|  6 |   S6 |  class2 |    66 |      3 |
|  2 |   S2 |  class2 |    33 |      1 |
| 13 |  S13 |  class2 |    33 |      1 |
|  4 |   S4 |  class3 |    65 |      5 |
| 11 |  S11 |  class3 |    65 |      5 |
|  5 |   S5 |  class3 |    43 |      3 |
| 12 |  S12 |  class3 |    43 |      3 |
| 15 |  S15 |  class3 |    25 |      1 |

Here is SQLFiddle demo

Answer 2

select t1.ID, t1.Name, t1.Section, t1.Marks, 
case ((SELECT COUNT(distinct MARKS) FROM students t2 WHERE t2.marks > t1.marks
and t1.Section = t2.Section) +1) when 1 then 5 when 2 then 3 else 1 end as Points
from students t1 join
     (select Section, substring_index(group_concat(distinct Marks order by Marks desc), ',', 3) as Marks3
      from students
      group by Section
     ) tsum
     on t1.Section = tsum.Section and
        find_in_set(t1.Marks, tsum.Marks3) > 0
ORDER BY Section, Marks DESC, ID ASC;

Output: Exactly as your demand. ;)

ID  NAME    SECTION MARKS   POINTS
1   S1  class1  55  5
7   S7  class1  32  3
3   S3  class1  25  1
10  S10 class2  78  5
14  S14 class2  78  5
6   S6  class2  66  3
2   S2  class2  33  1
13  S13 class2  33  1
4   S4  class3  65  5
11  S11 class3  65  5
5   S5  class3  43  3
12  S12 class3  43  3
15  S15 class3  25  1

fiddle