From: Jeremy Semeiks <jrs@denny.farviolet.com>
To: Heflin <hhogan@tampabay.rr.com>
To: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
Date: Wed, 21 Apr 2004 12:12:48 -0700
Message-ID: <20040421191248.GA18969@64.81.242.180> (raw)
In-Reply-To: <4086BD8E.90900@tampabay.rr.com>
References: <4086BD8E.90900@tampabay.rr.com>
On Wed, Apr 21, 2004 at 02:29:34PM -0400, Heflin wrote:
> OK, it's been a while since I've had to do anything remotely complex in
> SQL, so this may just be a pure brain block on my part.
>
...
>
> 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';
>
> auction_id | image_id | image_descr
> ------------+----------+-------------
> 1 | 1 | image 1
> 1 | 2 | image 2
> 2 | 3 | image 3
> 3 | 4 | image 4
> 3 | 5 | image 5
> 3 | 7 | image 8
> (6 rows)
>
> 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:
>
> auction_id | image_id | image_descr
> ------------+----------+-------------
> 1 | 2 | image 2
> 2 | 3 | image 3
> 3 | 7 | image 8
>
> Playing with the max() aggregate seems to be the correct path, but for
> the life of me I can't seem to get the syntax to the point that it
> produces what I need. Any help would be greatly appreciated!
A simple way to write this is to use DISTINCT ON combined with ORDER
BY. In this case, these clauses can substitute for the MAX aggregate:
select distinct on (auction.auction_id) auction.auction_id, image.image_id,
image.image_descr
from auction join image using (auction_id)
where auction.auction_owner = 'Mabel'
order by auction.auction_id, image.image_id desc;
(I haven't tested this.)
I think you need a subselect in there if you want to use the MAX
aggregate.
- Jeremy
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: jrs@denny.farviolet.com, hhogan@tampabay.rr.com
Subject: Re: Join issue on a maximum value
In-Reply-To: <20040421191248.GA18969@64.81.242.180>
* 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