Order by maximum value in a group

Question

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

So, if my entities are scrambled as follows:

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 as follows:

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

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

Hello,

Shaft

Answer 1

Here's how 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 with maxnote, he also orders a name. If there are connections, then it saves all entries for the given name together.

Answer 2

Answer CTE ...

 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; 

Answer 3

 SELECT t.name, t.note FROM @tbl t ORDER BY (SELECT MAX(note) FROM @tbl WHERE name = t.name) DESC , name , note DESC 

This is the easiest way to use PARTITION BY — this is a bit more syntax, and larger tables are more likely to work more efficiently.

Answer 4

A very simple way:

select a name, notice from NameNoteTable the order by name asc, note desc