按另一列选择具有最大列值的行组 [英] Select Rows with Maximum Column Value group by Another Column

问题描述

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

This should be a simple question, but I can't get it to work :(

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

How to select rows that have the maximum column value,as group by another column?

例如

我有以下表格定义:

ID
Del_Index
docgroupviewid

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

The issue now is that I want to group by results by docgroupviewid first, and then choose one row from each docgroupviewid group, depending on which one has the highest del_index.

我尝试了

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

但是，与其以正确的id退还给我，反而以最早的id退回给了我，且该组是具有相同docgroupviewid的.

But instead of return me with the correct id, it returns me with the earliest id from the group with the same docgroupviewid.

有什么想法吗?

推荐答案

我自己为此付出了很多努力，解决方案是以不同的方式考虑您的查询.

I've struggled with this many times myself and the solution is to think about your query differently.

我想要具有DocGroupViewID的所有行中Del_Index最高(最大)的每个DocGroupViewID行:

I want each DocGroupViewID row where the Del_Index is the highest(max) for all rows with that DocGroupViewID:

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时，它将变得更加复杂，因为从那时起，您需要某种方式来选择要显示的行.

It gets more complex when more than one row can have the same Del_Index, since then you need some way to choose which one to show.

您可以将RANK()或ROW_NUMBER()函数与CTE结合使用，以更好地控制结果，如下所示:

You can use the RANK() or ROW_NUMBER() functions with a CTE to get more control over the results, as follows:

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

If you have ways to sort out ties, just add it to the ORDER BY.

这篇关于按另一列选择具有最大列值的行组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！