Select Rows with Maximum Column Value group by Another Column

Question

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?

For example,

I have the following table definition:

ID
Del_Index
docgroupviewid

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.

I tried

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

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

Any ideas?

Answer 1

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

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 
)

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.

---

EDIT: wanted to follow up with another option

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

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

Answer 2

You will have to complicate your query a little bit:

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)