Re: Join issue on a maximum value

Heflin <hhogan(at)tampabay(dot)rr(dot)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