Gripe 2 with Oracle – Additional Columns in Group By

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. Alastair says:

    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. Sidd says:

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

Leave a Reply

Your email address will not be published. Required fields are marked *