按组中的最大值排序

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.

我想将我的结果分组为一列(NAME),然后按每个组的第二列(注释)排序,最后按照最高的顺序对组进行排序。

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).

按姓名分组,安德鲁首先出现因为他的最高音符是19,然后是托马斯(18)和弗兰克(16)。

Regards,

Val

4 个解决方案

#1

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;

#2

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.

除了通过maxnote订购外,它还按名称订购。如果存在联系,则它将给定名称的所有记录保存在一起。

#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.

这是最简单的方法,使用PARTITION BY只是稍微多一点的语法,在较大的表上可能会更有效地运行。

#4

Very simple way:

很简单的方法:

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

选择名称,注释来自NameNoteTable的名称asc,注意desc

#1