Re: Join issue on a maximum value

On Wed, Apr 21, 2004 at 16:28:10 -0400,
Heflin <hhogan(at)tampabay(dot)rr(dot)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.