On 2009-04-19, Martijn van Oosterhout <kleptog@xxxxxxxxx> wrote: > 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? anyway you could possibly write an agregate function that returns a copy of the row with the highest id? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
