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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
