Re: Join issue on a maximum value

Heflin <hhogan@tampabay.rr.com> writes:
> Bruno Wolff III 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 ORDER BY DESC is what forces the max image_id to be selected.  Read
the discussion of SELECT DISTINCT ON in the SELECT reference page; the
"weather report" example may be illuminating.
        regards, tom lane