mysql - First three Groups with Highest Marks should have specific points

Question

my table

+------+-------+---------+-------+--------+
| Name | Group1| Section | Marks | Points |
+------+-------+---------+-------+--------+
| S1   | G1    | class1  |    55 |        |
| S16  | G1    | class1  |    55 |        |
| S17  | G1    | class1  |    55 |        |
| S28  |       | class1  |    55 |        |
| S2   |       | class2  |    33 |        |
| S3   |       | class1  |    25 |        |
| S4   | G88   | class2  |    65 |        |
| S5   | G88   | class2  |    65 |        |
| S30  | G66   | class2  |    66 |        |
| S31  | G66   | class2  |    66 |        |
| S32  |       | class1  |    65 |        |
| S7   | G5    | class1  |    32 |        |
| S18  | G5    | class1  |    32 |        |
| S19  | G5    | class1  |    32 |        |
| S33  | G4    | class2  |    60 |        |
| S34  | G4    | class2  |    60 |        |
| S35  | G4    | class2  |    60 |        |
| S10  |       | class2  |    78 |        |
| S8   | G8    | class1  |    22 |        |
| S20  | G8    | class1  |    22 |        |
| S21  | G8    | class1  |    22 |        |
| S9   |       | class2  |    11 |        |
| S12  |       | class3  |    43 |        |
| S22  | G9    | class1  |    20 |        |
| S23  | G9    | class1  |    20 |        |
| S24  | G9    | class1  |    20 |        |
| S13  | G55   | class2  |    33 |        |
| S36  | G55   | class2  |    33 |        |
| S14  |       | class2  |    78 |        |
| S25  | G10   | class1  |    55 |        |
| S26  | G10   | class1  |    55 |        |
| S27  | G10   | class1  |    55 |        |
+------+-------+---------+-------+--------+

SQL FIDDLE : http://www.sqlfiddle.com/#!2/5ce6c/1

I am trying to give specific points to first 3 groups with highest marks in each Section. I would like to add 5 points to each student in the 1st highest groups, 3 points for 2nd highest and 1 points for 3rd highest group. .Duplicate Marks may occur for group.

I am using following code, this code works fine for individual students, dont know how to give points to the Group.

select 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 where Section = 'class1' 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

My final output looks for a Section.

 +---------------------------------------------+
 | | Name | Group1| Section | Marks | Points | |
 +---------------------------------------------+
 | | S1   | G1    | class1  |    55 |    5   | |
 | | S16  | G1    | class1  |    55 |    5   | |
 | | S17  | G1    | class1  |    55 |    5   | |
 | | S28  |       | class1  |    55 |        | |
 | | S2   |       | class2  |    33 |        | |
 | | S3   |       | class1  |    25 |        | |
 | | S4   | G88   | class2  |    65 |        | |
 | | S5   | G88   | class2  |    65 |        | |
 | | S30  | G66   | class2  |    66 |        | |
 | | S31  | G66   | class2  |    66 |        | |
 | | S32  |       | class1  |    65 |        | |
 | | S7   | G5    | class1  |    32 |    3   | |
 | | S18  | G5    | class1  |    32 |    3   | |
 | | S19  | G5    | class1  |    32 |    3   | |
 | | S33  | G4    | class2  |    60 |        | |
 | | S34  | G4    | class2  |    60 |        | |
 | | S35  | G4    | class2  |    60 |        | |
 | | S10  |       | class2  |    78 |        | |
 | | S8   | G8    | class1  |    22 |   1    | |
 | | S20  | G8    | class1  |    22 |   1    | |
 | | S21  | G8    | class1  |    22 |   1    | |
 | | S9   |       | class2  |    11 |        | |
 | | S12  |       | class3  |    43 |        | |
 | | S22  | G9    | class1  |    20 |        | |
 | | S23  | G9    | class1  |    20 |        | |
 | | S24  | G9    | class1  |    20 |        | |
 | | S13  | G55   | class2  |    33 |        | |
 | | S36  | G55   | class2  |    33 |        | |
 | | S14  |       | class2  |    78 |        | |
 | | S25  | G10   | class1  |    55 |   5    | |
 | | S26  | G10   | class1  |    55 |   5    | |
 | | S27  | G10   | class1  |    55 |   5    | |
 +---------------------------------------------+

Please help me.

Answer 1

That was challenging.

To solve it I used several methods:

1. A CASE statement to convert the group's place in the top 3 into points.
2. Rows numbering with a variable.
3. INNER AND LEFT JOIN to merge the results together.

The following query was tested on your fiddle and works:

SELECT t1.`id`,  t1.`name`,  t1.`group1`,
       t1.`section`, t1.`MARKS`, `t_group_points`.`points`

FROM   `students` t1

#--- Join  groups' points to the students
LEFT JOIN (
    (
        #---- Join all groups and give points to top 3 avg's groups ----
        SELECT `t4`.`group1`, `t_points`.`points`
        FROM   (SELECT   `t3`.`group1`, AVG(`t3`.`marks`) AS `avg`
                FROM     `students` `t3`
                WHERE    (`t3`.`section` = 'class1') AND
                         (`t3`.`group1` IS NOT NULL)
                GROUP BY `t3`.`group1`) `t4`

        INNER JOIN (
              #---------- Select top 3 avarages ----------
              (SELECT `top`.`avg`,
                      #-- Convert row number to points ---
                      CASE @curRow := @curRow + 1  
                           WHEN '1' THEN 5
                           WHEN '2' THEN 3
                           WHEN '3' THEN 1
                           ELSE NULL END 'points'

              FROM (SELECT DISTINCT `t_avg`.`avg`
                    FROM   (SELECT   `t2`.`group1`, AVG(`t2`.`marks`) AS `avg`
                            FROM     `students` `t2`
                            WHERE    (`t2`.`section` = 'class1') AND
                                     (`t2`.`group1` IS NOT NULL)
                            GROUP BY `group1`) `t_avg`
                    ORDER BY `avg` DESC
                    LIMIT 0, 3) `top`, (SELECT @curRow:=0) r
              ) AS `t_points`)
         ON (`t_points`.`avg` = `t4`.`avg`)      
    ) AS `t_group_points`)
ON (`t_group_points`.`group1` = `t1`.`group1`)