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

-

this question has answer here:

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 question before, according comments tried improve question more clear.

i trying give specific points first 3 groups highest marks in each section. add 5 points each student got highest mark in section, 3 points 2nd highest marks , 1 points 3rd highest group. duplicate marks may occur group.

eg: in class1 - group 'g1' , 'g10' got '55' , got highest mark in 'class1'. give '5' points students in group 'g1' and' g10'.

group g5 got second highest marks, student in group should give 3 points. same way g8 - 1 point.

plz note : student doesnt have group id student participating single item, not consider records.

i using following code, code works fine individual students, dont know how give points group members.

select t1.name, t1.section, t1.marks mytable t1 join  (select section, substring_index(group_concat (distinct marks order marks desc),     ',', 3) marks3  mytable section = 'class1' group section ) tsum   on t1.section = tsum.section , find_in_set(t1.marks, tsum.marks3) > 0  order section, marks desc, id desc

my final output looks section. plz give 1 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 me..

this answer given itay

select t1.`id`,  t1.`name`,  t1.`group1`,        t1.`section`, t1.`marks`, `t_group_points`.`points`    `students` t1  left join (     (         select `t4`.`group1`, `t_points`.`points`           (select   `t3`.`group1`, avg(`t3`.`marks`) `avg`                     `students` `t3`                    (`t3`.`section` = 'class1') ,                          (`t3`.`group1` not null)                 group `t3`.`group1`) `t4`          inner join (               (select `top`.`avg`,                       case @currow := @currow + 1                              when '1' 5                            when '2' 3                            when '3' 1                            else null end 'points'                (select `t_avg`.`avg`                       (select   `t2`.`group1`, avg(`t2`.`marks`) `avg`                                 `students` `t2`                                (`t2`.`section` = 'class1') ,                                      (`t2`.`group1` not null)                             group `group1`) `t_avg`                     group `t_avg`.`avg`                     order `avg` desc                     limit 0, 3) `top`, (select @currow:=0) r               ) `t_points`)          on (`t_points`.`avg` = `t4`.`avg`)           ) `t_group_points`) on (`t_group_points`.`group1` = `t1`.`group1`)

Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more