按另一列分组选择具有最大列值的行

Question

这应该是一个简单的问题，但我无法让它工作:(

如何选择具有最大列值的行，作为另一列的分组？

例如，

我有以下表定义:

ID
Del_Index
docgroupviewid

现在的问题是，我想首先按docgroupviewid对结果进行分组，然后从每个docgroupviewid组中选择一行，具体取决于哪一行具有最高的del_index。

我尝试过

SELECT docgroupviewid, max(del_index),id FROM table
group by docgroupviewid

但是它并没有返回正确的id，而是返回与具有相同docgroupviewid的组中最早的id。 有什么想法吗？

- Graviton

---

当有两个或更多的del_index值匹配同一docgroupviewid的最大值时，您如何打破平局？ - OMG Ponies

不确定...你有什么想法吗？ - Graviton

经典例子，让我也遇到了很多麻烦！ - nawfal

Answer 1

我自己也曾经遇到过这个问题，解决方法是要用不同的思路来考虑你的查询。

我的目标是获取每个DocGroupViewID行中Del_Index值最高（即max）的那一行：

SELECT
    T.DocGroupViewID,
    T.Del_Index,
    T.ID
FROM MyTable T
WHERE T.Del_Index = (
    SELECT MAX( T1.Del_Index ) FROM MyTable T1
    WHERE T1.DocGroupViewID = T.DocGroupViewID 
)

当多行具有相同的Del_Index时，情况会变得更加复杂，因为您需要某种方式来选择要显示哪一行。

---

编辑：想跟进另一个选项

您可以使用RANK()或ROW_NUMBER()函数与CTE一起使用，以获得对结果更多的控制，如下所示：

-- fake a source table
DECLARE @t TABLE (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Del_Index int,
    DocGroupViewID int
)

INSERT INTO @t
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 3

-- show our source
SELECT * FROM @t

-- select using RANK (can have duplicates)
;WITH cteRank AS
(
    SELECT
        DocGroupViewID,
        Del_Index,
        ID,
        RANK() OVER
            (PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
        AS RowRank,
        ROW_NUMBER() OVER
            (PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
        AS RowNumber
    FROM @t
)
SELECT *
FROM cteRank
WHERE RowRank = 1

-- select using ROW_NUMBER
;WITH cteRowNumber AS
(
    SELECT
        DocGroupViewID,
        Del_Index,
        ID,
        RANK() OVER
            (PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
        AS RowRank,
        ROW_NUMBER() OVER
            (PARTITION BY DocGroupViewID ORDER BY Del_Index DESC)
        AS RowNumber
    FROM @t
)
SELECT *
FROM cteRowNumber
WHERE RowNumber = 1

如果您有解决方案来排序，只需将其添加到 ORDER BY 中即可。

- Timothy Walters

---

谢谢你的精彩示例，Tim。 - d-_-b

Answer 2

您需要稍微复杂一下您的查询：

select a.docgroupviewid, a.del_index, a.id from table a
    where a.del_index = (select max(b.del_index) from table
        where b.docgroupviewid = a.docgroupviewid)

- Alterlife