問題描述
按組中的最大值排序 (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
參考解法
方法 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.
方法 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;
方法 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 simplest way, using PARTITION BY is only slightly more syntax and on larger tables would likely run more efficiently.
方法 4:
Very simple way:
select name, note from NameNoteTable order by name asc, note desc
(by Valerio Volga、Gordon Linoff、Eric J. Price、Louis Ricci、DINESH PATIL)