In the MySQL world, I can do this:
select a, b, c, max(d) from e group by a

In the oracle world that results in:
ORA-00979: not a GROUP BY expression

To overcome this, you guessed it, nested queries again:
select a, b, c, d from e, (select a, max(d) as d from e group by a) e2 where e.a=e2.a and e.d=e2.d
(There might be potential problems with this but that is beyond the scope of my gripe 🙂 )

2 thoughts on “Gripe 2 with Oracle – Additional Columns in Group By”

  1. Never used MySQL so I don’t know what that query ‘means’ but in Oracle you would need to group by all the non-aggregated columns as such:

    select a, b, c, max(d) from e group by a, b, c

    It depends on what your b and c columns actually are in your real situation but this may well be equivalent to the solution you blogged.

  2. Alastair, this is not the same. Try it out and see.

Leave a Reply