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 🙂 )
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.
Alastair, this is not the same. Try it out and see.