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.

According to the marks, in class1 - 55 is the highest marks. So 5 points should go to the Group G1 & G10. second highest is 32 marks. So 3 points should go to the group G5. And third highest is 22. So 1 point to G8. Hope you understand — user2594154, Aug 23 '13 at 16:22
Student S32 got 65, but belongs to no group, so what do we do with that? And G10 only 22, so how come they get 5 points? — Strawberry, Aug 23 '13 at 16:25
hi good question, that student is participating individually. Do not consider the student who doesnt have any GROUP. If Group is empty that students are participating in single item. I could give points on individual student, the code i have mentioned will works. i am not sure about the group competition. — user2594154, Aug 23 '13 at 16:30
The data set provided here differs from the one provided in the sqlfiddle. VERY CONFUSING!! — Strawberry, Aug 23 '13 at 16:35
Kindly look this question http://stackoverflow.com/questions/18415682/first-three-groups-with-highest-marks-should-have-specific-points-5-3-1 — user2594154, Aug 24 '13 at 06:26

score 3 · Answer 1 · edited May 23 '17 at 12:14

3

That was challenging.

To solve it I used several methods:

A CASE statement to convert the group's place in the top 3 into points.
Rows numbering with a variable.
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`)

edited May 23 '17 at 12:14

Community

1
1

answered Aug 23 '13 at 14:44

Itay

16,601
2
51
72

hello itay, that code is not working. plz check fiddle, I have added it. – user2594154 Aug 23 '13 at 15:52
thanks Itay. Great job. Your code is absolutely fine when there is no duplicate marks. eg, G1, G10 have got same marks of 55 any they are the highest. So both group should get points of 5. in this code G10 is getting mark 3 only. – user2594154 Aug 24 '13 at 01:37
I will add this question again – user2594154 Aug 24 '13 at 05:20
Kindly look this question http://stackoverflow.com/questions/18415682/first-three-groups-with-highest-marks-should-have-specific-points-5-3-1 – user2594154 Aug 24 '13 at 06:27
Great Great Great my sweet friend. this code works absolutley fine.. Great JOB. I cant express my thanks to you. Love you lot – user2594154 Aug 24 '13 at 06:51
hi itay, a small problem found when tried your code in the application. this code will erase the existing values in other records. eg i have given some values for ID 5,7,8,9 and 10. when i run this code those values are gone. http://www.sqlfiddle.com/#!2/78864f/2 – user2594154 Aug 29 '13 at 00:39
Sorry I can't see the problem. When I run this query I can see 5,7-10 in the results – Itay Aug 29 '13 at 07:16
i am not offending you,you helped me a lot. could you pls check this fiddle http://www.sqlfiddle.com/#!2/78864f/2 – user2594154 Aug 29 '13 at 14:24

score 1 · Accepted Answer · edited May 23 '17 at 12:06

I have found a solution to list the groups with their allocated points but I was having a hard time storing the result back into myTable, i.e. doing an UPDATE on the table. I managed at last (see bottom of post!!).

But first, here is the group score-table generator (group overview):

SELECT mg,ms,mm,
  CASE WHEN @s=ms THEN 
    CASE WHEN @m=mm THEN @i 
         WHEN @i>2 THEN @i:=@i-2 
         ELSE null END
  ELSE @i:=5 END pt,
  @g:=mg gr,@s:=ms,@m:=mm 
FROM (
  SELECT group1 mg,section ms,max(marks) mm FROM mytable
  WHERE group1>''
  GROUP BY group1,section
) m 
ORDER BY ms,mm desc,mg

http://sqlfiddle.com/#!2/bea2a2/1

It gives me this list:

|  MG |     MS | MM |     PT |  GR | @S:=MS | @M:=MM |
------------------------------------------------------
|  G1 | class1 | 55 |      5 |  G1 | class1 |     55 |
| G10 | class1 | 55 |      5 | G10 | class1 |     55 |
|  G5 | class1 | 32 |      3 |  G5 | class1 |     32 |
|  G8 | class1 | 22 |      1 |  G8 | class1 |     22 |
|  G9 | class1 | 20 | (null) |  G9 | class1 |     20 |
| G66 | class2 | 66 |      5 | G66 | class2 |     66 |
| G88 | class2 | 65 |      3 | G88 | class2 |     65 |
|  G4 | class2 | 60 |      1 |  G4 | class2 |     60 |
| G55 | class2 | 33 | (null) | G55 | class2 |     33 |

I am back (26.08.2013, after having asked for help myself, see here) and can now provide the full answer:

SET @s:=@m:=@i:='a'; -- variables *MUST* be "declared" in some
                     -- way, otherwise UPDATE will not work!

UPDATE mytable INNER JOIN 
(SELECT mg,ms,mm,
   CASE WHEN @s=ms THEN 
     CASE WHEN @m=mm THEN @i 
          WHEN @i>2 THEN @i:=@i-2 
          ELSE null END
   ELSE @i:=5 END pt,
   @s:=ms,@m:=mm 
 FROM (
   SELECT group1 mg,section ms,max(marks) mm FROM mytable
   WHERE group1>''
   GROUP BY group1,section
 ) m 
 ORDER BY ms,mm desc,mg
) t ON mg=group1 AND ms=section AND mm=marks
SET Points=pt

see here http://sqlfiddle.com/#!2/bb7f2

And finally - off-topic:

Dear user @user2594154, why have you bombarded this board with the same question 8 times?!?

Add specific points on highest marks Find sum of points and grouping
First three Groups with Highest Marks should have specific points (this post)
Grouping results by name and points Group each Sections by Points and
CompetitionName descending order Find highest points of two students
in a section Display 3 Maximum values of a Column, include duplicate
values with condition First three Groups with Highest Marks should
have specific points 5,3, 1 [duplicate]

It would be much more helpful for everybody if you had kept your question in one post, explaining exactly what you want and also what you have tried yourself (!!). Then, in the course of the answering process, it is possible to edit it, making it more precise. New questions should only be posted, if their subject is actually different, see here.

No hard feelings - I learnt a lot in the process of solving this problem of yours. ;-)

I thought about using the equal gap between the points (5, 3, 1) to decrease their number, but I've realized it's probably not flexible enough. And you're already using a CASE statement, so you can easily fix it (see my answer). Don't you agree? — Itay, Aug 23 '13 at 17:45
@Itay Yes of course, that is certainly possibble and more flexible, but my main concern about my approach is that I was/am not able to actually store my result. Neither can I insert it into a temporary table nor can I use it in an join with an update statement ... very frustrating. — Carsten Massmann, Aug 23 '13 at 21:02
Kindly look this question http://stackoverflow.com/questions/18415682/first-three-groups-with-highest-marks-should-have-specific-points-5-3-1 — user2594154, Aug 24 '13 at 06:28

Joachim Isaksson · Answer 3 · 2013-08-24T08:25:07.660

You can do an update join to achieve what you want;

UPDATE students
JOIN (
  SELECT marks, (@sp:=@sp-2) a
  FROM (
    SELECT distinct marks FROM students
    WHERE section='class1' AND group1 IS NOT NULL
    GROUP BY group1 ORDER BY marks DESC LIMIT 3
  ) b, (SELECT @sp:=7) c
) d
SET students.points = d.a 
WHERE students.marks = d.marks
  AND section='class1'
  AND group1 IS NOT NULL;

(posted this answer also to your second question where you seemed to ask for an update statement without noticing that it was a duplicate, moved it here)

An SQLfiddle to test with.

First three Groups with Highest Marks should have specific points

3 Answers3

Linked

Related