user2371765 user2371765 - 10 days ago 5
SQL Question

GROUP BY a small subset of returned fields

Suppose I want to select from a table ABC containing fields A,B,C,D,E where E is a numeric field.

For a given combination of

A
and
B
I want to return the row containing the maximum value of
E
. How do I do this? What do I
GROUP BY
?

If I try to GROUP by A,B while putting max only around E, I get the "
not a GROUP BY expression
" error, but I do want to GROUP only by A,B.

I tried

select A,B,C,D,max(E)
from ABC
group by A,B


and this does not work.

Answer

This does not require group by. A typical way is:

select abc.*
from abc
where abc.e = (select max(abc2.e) from abc abc2 where abc.a = abc2.a and abc.b = abc2.a);

However, this will return duplicates if multiple rows have the same maximum e value. Another method uses ANSI standard window functions:

select abc.*
from (select abc.*,
             row_number() over (partition by a, b order by e desc) as seqnum
      from abc
     ) abc
where seqnum = 1;

This returns one (arbitrary) row, if there are duplicate maximum e values. You can use rank() or dense_rank() instead of row_number() if you want the duplicates using this method.