First three Groups with Highest Marks should have specific points 5,3, 1

Question

my table

+------+--------+---------+-------+--------+
| NAME | GROUP1 | SECTION | MARKS | POINTS |
+------+--------+---------+-------+--------+
| S1   | G1     | class1  |    55 | (null) |
| S16  | G1     | class1  |    55 | (null) |
| S17  | G1     | class1  |    55 | (null) |

| S2   | (null) | class2  |    33 | (null) |

| S25  | G10    | class1  |    55 | (null) |
| S26  | G10    | class1  |    55 | (null) |

| S4   | G88    | class2  |    65 | (null) |
| S5   | G88    | class2  |    65 | (null) |
| S32  | (null) | class1  |    65 | (null) |

| S7   | G5     | class1  |    32 | (null) |
| S18  | G5     | class1  |    32 | (null) |

| S10  | (null) | class2  |    78 | (null) |

| S8   | G8     | class1  |    22 | (null) |
| S20  | G8     | class1  |    22 | (null) |

| S22  | G9     | class1  |    20 | (null) |
| S23  | G9     | class1  |    20 | (null) |

| S13  | G55    | class2  |    33 | (null) |
| S36  | G55    | class2  |    33 | (null) |
+------+--------+---------+-------+--------+

SQL FIDDLE : http://www.sqlfiddle.com/#!2/f4b54/1/0

I have raised this question before, according to the comments I tried to improve the question more clear.

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 who got highest Mark in a Section, 3 points for 2nd highest Marks and 1 points for 3rd highest group. Duplicate Marks may occur for group.

eg: in class1 - Group 'G1' and 'G10' got '55' and they got highest mark in 'class1'. So i would like to give '5' points to all students in Group 'G1' and' G10'.

Group G5 got second highest marks, so all student in that group should give 3 points. Same way G8 - 1 point.

plz note : any student who doesnt have Group ID that student is participating single item, do not consider that records.

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

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. Plz give only one Section (class1)

+------+--------+---------+-------+--------+
| NAME | GROUP1 | SECTION | MARKS | POINTS |
+------+--------+---------+-------+--------+
| S1   | G1     | class1  |    55 |    5   |
| S16  | G1     | class1  |    55 |    5   |
| S17  | G1     | class1  |    55 |    5   |

| S2   | (null) | class2  |    33 | (null) |

| S25  | G10    | class1  |    55 |    5   |
| S26  | G10    | class1  |    55 |    5   |

| S4   | G88    | class2  |    65 | (null) |
| S5   | G88    | class2  |    65 | (null) |

| S32  | (null) | class1  |    65 | (null) |

| S7   | G5     | class1  |    32 |    3   |
| S18  | G5     | class1  |    32 |    3   |

| S10  | (null) | class2  |    78 | (null) |

| S8   | G8     | class1  |    22 |    1   |
| S20  | G8     | class1  |    22 |    1   |

| S22  | G9     | class1  |    20 | (null) |
| S23  | G9     | class1  |    20 | (null) |

| S13  | G55    | class2  |    33 | (null) |
| S36  | G55    | class2  |    33 | (null) |
+------+--------+---------+-------+--------+

Kindly Help me..

You just asked the same the question! http://stackoverflow.com/questions/18404726/first-three-groups-with-highest-marks-should-have-specific-points — Bohemian, Aug 24 '13 at 06:31

score -1 · Answer 1 · edited May 23 '17 at 12:28

This answer is given by Itay

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

FROM   `students` t1

LEFT JOIN (
    (
        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`.`avg`,
                      CASE @curRow := @curRow + 1  
                           WHEN '1' THEN 5
                           WHEN '2' THEN 3
                           WHEN '3' THEN 1
                           ELSE NULL END 'points'

              FROM (SELECT `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`
                    GROUP BY `t_avg`.`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`)

First three Groups with Highest Marks should have specific points 5,3, 1

1 Answers1

Linked