Re: Join issue on a maximum value - Mailing list pgsql-sql

From Bruno Wolff III
Subject Re: Join issue on a maximum value
Date
Msg-id 20040421185122.GA31195@wolff.to
Whole thread Raw
In response to Join issue on a maximum value  (Heflin <hhogan@tampabay.rr.com>)
Responses Re: Join issue on a maximum value
List pgsql-sql
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
ONauction.auction_id = image.auction_id WHERE auction.auction_owner = 'Mabel' ORDER BY auction.auction_id,
image.image_idDESC
 
;

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.


pgsql-sql by date:

Previous
From: Heflin
Date:
Subject: Join issue on a maximum value
Next
From: Kemin Zhou
Date:
Subject: Server Side C programming Environment Set up