Skip site navigation (1) Skip section navigation (2)

Re: Join issue on a maximum value

From: Jeremy Semeiks <jrs(at)denny(dot)farviolet(dot)com>
To: Heflin <hhogan(at)tampabay(dot)rr(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Join issue on a maximum value
Date: 2004-04-21 19:12:48
Message-ID: 20040421191248.GA18969@64.81.242.180 (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Edmund BaconDate: 2004-04-21 19:21:54
Subject: Re: Join issue on a maximum value
Previous:From: Bruno Wolff IIIDate: 2004-04-21 18:51:22
Subject: Re: Join issue on a maximum value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group