From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heflin <hhogan@tampabay.rr.com>
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
Date: Wed, 21 Apr 2004 23:30:58 -0400
Message-ID: <18805.1082604658@sss.pgh.pa.us> (raw)
In-Reply-To: <4086D95A.5000609@tampabay.rr.com>
References: <4086BD8E.90900@tampabay.rr.com>
<20040421185122.GA31195@wolff.to>
<4086D95A.5000609@tampabay.rr.com>
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
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: tgl@sss.pgh.pa.us, hhogan@tampabay.rr.com, bruno@wolff.to
Subject: Re: Join issue on a maximum value
In-Reply-To: <18805.1082604658@sss.pgh.pa.us>
* 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