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
not a GROUP BY expression
group by A,B
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
dense_rank() instead of
row_number() if you want the duplicates using this method.