From: Bruno Wolff III <bruno@wolff.to>
To: Heflin <hhogan@tampabay.rr.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
Date: Wed, 21 Apr 2004 13:51:22 -0500
Message-ID: <20040421185122.GA31195@wolff.to> (raw)
In-Reply-To: <4086BD8E.90900@tampabay.rr.com>
References: <4086BD8E.90900@tampabay.rr.com>
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.
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: bruno@wolff.to, hhogan@tampabay.rr.com
Subject: Re: Join issue on a maximum value
In-Reply-To: <20040421185122.GA31195@wolff.to>
* 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