Skip site navigation (1) Skip section navigation (2)

Re: DISTINCT ON without ORDER BY

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: DISTINCT ON without ORDER BY
Date: 2009-04-21 12:11:26
Message-ID: gskd5e$ake$4@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-general
On 2009-04-19, Martijn van Oosterhout <kleptog(at)svana(dot)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?



In response to

Responses

pgsql-general by date

Next:From: Diego SchulzDate: 2009-04-21 13:26:42
Subject: Re: Error stranger
Previous:From: milos.babicDate: 2009-04-21 11:38:39
Subject: Re: This is my first template

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group