user2371765 - 1 month ago 16

SQL Question

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`

`B`

`E`

`GROUP BY`

If I try to GROUP by A,B while putting max only around E, I get the "

`not a GROUP BY expression`

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.