Re: DISTINCT ON without ORDER BY - Mailing list pgsql-general

Martijn van Oosterhout <kleptog@svana.org> 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'
languagesql; 
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