CODEWITHSUNDEEP
sqlsql-serversql-server-2008group-bysql-order-by
Valerio Volga
Valerio Volga

Reputation: 71

Order by the max value in the group

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

Upvotes: 6

Views: 9534

Answers (4)

DINESH PATIL
DINESH PATIL

Reputation: 1

Very simple way:

select name, note from NameNoteTable order by name asc, note desc

Upvotes: 0

Louis Ricci
Louis Ricci

Reputation: 21086

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 simplest way, using PARTITION BY is only slightly more syntax and on larger tables would likely run more efficiently.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 4

Eric J. Price
Eric J. Price

Reputation: 2785

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;

Upvotes: 3

Related Questions