From: Heflin <hhogan@tampabay.rr.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
Date: Wed, 21 Apr 2004 16:28:10 -0400
Message-ID: <4086D95A.5000609@tampabay.rr.com> (raw)
In-Reply-To: <20040421185122.GA31195@wolff.to>
References: <4086BD8E.90900@tampabay.rr.com>
<20040421185122.GA31195@wolff.to>
Bruno Wolff III wrote:
>On Wed, Apr 21, 2004 at 14:29:34 -0400,
> Heflin <hhogan@tampabay.rr.com> wrote:
>
>
>>So a basic JOIN gets this:
>>
>>SELECT 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';
>>
>>Now the problem: I can't seem to remember how to get only the max value
>>for the image_id for each auction_id so that the result set would be:
>>
>>
>
>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 more standard way to do it would be joining auction and image
>with a group by and max to get the highest image_id and then joining
>that result to image again to get the corresponding description.
>
>
>
Thanks!
I was actually trying to do it the more standard way, but I've been
bungling up the syntax. I'm going to play with that some more, since it
might be useful elsewhere.
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?
Thanks again,
-Heflin
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.3K) download
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: hhogan@tampabay.rr.com, bruno@wolff.to
Subject: Re: Join issue on a maximum value
In-Reply-To: <4086D95A.5000609@tampabay.rr.com>
* 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