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

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)

Answer 2

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.