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 <klep...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
