mysql - Sub select of row that has max column -

mysql - Sub select of row that has max column -

i'm trying sounds simple have been going round in circles little it..

i have stored procedure works required missing 1 bit of functionality, homecoming name corrosponding max calculation...

so homecoming

average calculation & max calculation want homecoming 'the name column' max value.

here illustration of sp, apologies may not seem natural have had rename , omit non relevant bits may seem little contrived::

select ifnull(round(avg(table1.totalcapacitypercentageusage / table1.totalsnapshotsfortimesegment), 2), 0.0) totalavgcapacitypercentageusage, ifnull(round(max(table1.maxcapacitypercentageusage), 2), 0.0) totalmaxcapacitypercentageusage, -- todo homecoming queuestmp.queuename max calculation (this more 1 row, going utilize following: -- (select group_concat(queuestmp.queuename separator ' ') ensure 1 field returned.. table1 inner bring together queuestmp on queuestmp.queueid = table1.queueid right bring together timestmp on table1.timesegment = timestmp.quarterhour , table1.date = date(timestmp.startofrangeutc) grouping timestmp.quarterhour;

i started doing sub select seems have repeat of joins, , grouping (seems not possible because that's having for)..

can guide me in right direction how can achieved?

thanks in advance.

working solution

group_concat(distinct queuestmp.queuename order mycolumn desc separator ':') maxcolumnqueuename,

i'm not sure i'm on right way. need queuename of row max - calculation. utilize group_concat order by of calculation , substring_index first element of list.

substring_index( group_concat(distinct queuestmp.queuename order `maxcalculation` desc) separator ':', ':', 1 )

additional question. sorry unfortunately max comment space has reached. here query. used illustration - query sub , select queueid comma-separated list , max(maxcolumn) additional. after bring together queue-table 1 time again queueid , maxcolumn. can't guarantee if works.

select sub.totalavgcapacitypercentageusage, sub.totalmaxcapacitypercentageusage, group_concat(distinct queuestmp.queuename order mycolumn desc separator ':') maxcolumnqueuename from( select timestmp.quarterhour, ifnull( round( avg( table1.totalcapacitypercentageusage / table1.totalsnapshotsfortimesegment ), 2 ), 0.0 ) totalavgcapacitypercentageusage, ifnull( round( max(table1.maxcapacitypercentageusage), 2 ), 0.0 ) totalmaxcapacitypercentageusage, max(queuestmp.maxcolumn) maxcolumn, group_concat(distinct queueid) queueid table1 inner bring together queuestmp on queuestmp.queueid = table1.queueid right bring together timestmp on table1.timesegment = timestmp.quarterhour , table1.date = date(timestmp.startofrangeutc) grouping timestmp.quarterhour ) sub left bring together queuestmp on queuestmp.queueid in(sub.queueid) , queuestmp.maxcolumn = sub.maxcolumn

mysql stored-procedures