user2371765 user2371765 - 1 year ago 78
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

I want to return the row containing the maximum value of
. How do I do this? What do I

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download