Re: DISTINCT ON without ORDER BY

On 2009-04-19, Martijn van Oosterhout <kleptog@svana.org> 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?