Hi,
I was going through the queries of an SQL application and came across
queries like:
SELECT * FROM foo
WHERE id in (SELECT max(id) FROM foo GROUP BY bar);
I thought, here's a case where this could be better written using
DISTINCT ON, since then you avoid the self-join:
SELECT DISTINCT ON (bar) * FROM
ORDER BY bar, id DESC;
However, this was slower because the original query could use a hash
aggregate whereas the new query needed to do a sort. The way DISTINCT
ON is defined it requires an ORDER BY whereas semantically the ordering
on the first attribute is just a by product of the old implementation.
Is there a way to acheive the above result without a sort and without a
self-join?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.