sql - Order by the max value in the group -

i group results 1 column (name), order second column (note) each group, , order groups highest note have.

so, if entities scrambled these:

name         note andrew       19 thomas       18 andrew       18 andrew       17 frank        16  frank        15 thomas       14  thomas       12 frank        5 

i them ordered this:

name         note andrew       19 andrew       18 andrew       17 thomas       18 thomas       14  thomas       12 frank        16  frank        15 frank        5 

grouped name, andrew appearing first because highest note 19, thomas (18) , frank (16).

regards,

val

cte answer...

create  table namenotetable (name varchar(10), note int);  insert  namenotetable select  'andrew', 19 union   select  'andrew', 18 union   select  'andrew', 17 union   select  'thomas', 18 union   select  'thomas', 14 union   select  'thomas', 12 union   select  'frank', 16 union   select  'frank', 15;     cte (         select  row_number() on (order max(note) desc) tid,                 name,                 max(note) maxnote            namenotetable         group   name ) select  nnt.name, nnt.note    namenotetable nnt join    cte c         on  nnt.name = c.name order   tid, note desc;