Order by the max value in the group

Question

I would like to group my results by one column (NAME), then order by a second column (NOTE) for each group, and finally order the groups by the highest NOTE they have.

So, if my entities are scrambled like these:

NAME         NOTE
Andrew       19
Thomas       18
Andrew       18
Andrew       17
Frank        16 
Frank        15
Thomas       14 
Thomas       12
Frank        5

I would like them to be ordered like this:

NAME         NOTE
Andrew       19
Andrew       18
Andrew       17
Thomas       18
Thomas       14 
Thomas       12
Frank        16 
Frank        15
Frank        5

grouped by name, with Andrew appearing first because his highest note is 19, then Thomas (18) and Frank (16).

Regards,

Val

Answer 1

Here is a way to do it using window functions:

select name, note
from (select t.*, max(note) over (partition by name) as maxnote
      from t
     ) t
order by maxnote desc, name

In addition to ordering by the maxnote, it also orders by the name. If there are ties, then it keeps all the records for a given name together.

Answer 2

CTE answer...

Create  Table NameNoteTable (Name Varchar(10), Note Int);

Insert  NameNoteTable
Select  'Andrew', 19
Union   All
Select  'Andrew', 18
Union   All
Select  'Andrew', 17
Union   All
Select  'Thomas', 18
Union   All
Select  'Thomas', 14
Union   All
Select  'Thomas', 12
Union   All
Select  'Frank', 16
Union   All
Select  'Frank', 15;

With    cte As
(
        Select  Row_Number() Over (Order By Max(Note) Desc) As tID,
                Name,
                Max(Note) As MaxNote
        From    NameNoteTable
        Group   By Name
)
Select  nnt.Name, nnt.Note
From    NameNoteTable nnt
Join    cte c
        On  nnt.Name = c.Name
Order   By tID, Note Desc;