From: Bruno Wolff III <bruno@wolff.to>
To: Heflin <hhogan@tampabay.rr.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
Date: Wed, 21 Apr 2004 20:51:45 -0500
Message-ID: <20040422015145.GB2160@wolff.to> (raw)
In-Reply-To: <4086D95A.5000609@tampabay.rr.com>
References: <4086BD8E.90900@tampabay.rr.com>
<20040421185122.GA31195@wolff.to>
<4086D95A.5000609@tampabay.rr.com>
On Wed, Apr 21, 2004 at 16:28:10 -0400,
Heflin <hhogan@tampabay.rr.com> wrote:
> >
> >The postgres specific way of doing this is:
> >SELECT DISTINCT ON (auction.auction_id)
> > auction.auction_id, image.image_id, image.image_descr
> > FROM auction JOIN image ON auction.auction_id = image.auction_id
> > WHERE auction.auction_owner = 'Mabel'
> > ORDER BY auction.auction_id, image.image_id DESC
> >;
>
> The thing that disturbs me about your syntax is that I don't really see
> an assurance that I'll get the correct image_id. Any chance you can
> tell me why this works?
The postgres semantic is that when dinstinct on is combined with order by
the first distinct row in the order defined by the order by is the one
returned. This is described in the documentation and there is an example
of a query taking advantage of this.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, hhogan@tampabay.rr.com
Subject: Re: Join issue on a maximum value
In-Reply-To: <20040422015145.GB2160@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox