Martijn van Oosterhout <kleptog@xxxxxxxxx> writes: > SELECT * FROM foo > WHERE id in (SELECT max(id) FROM foo GROUP BY bar); > > Is there a way to acheive the above result without a sort and without a > self-join? Something like SELECT bar, (magic_agg_func(foo)).* FROM foo GROUP BY bar where you define an aggregate function magic_agg_func to remember the whole record for the largest value of id. Something like: postgres=# create function magic_transition(a,a) returns a as 'select case when $1.aid > $2.aid then $1 else $2 end' language sql; postgres=# create aggregate magic (a) (sfunc = magic_transition, stype = a); Not sure it'll be faster though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
