Join issue on a maximum value - Mailing list pgsql-sql
From | Heflin |
---|---|
Subject | Join issue on a maximum value |
Date | |
Msg-id | 4086BD8E.90900@tampabay.rr.com Whole thread Raw |
Responses |
Re: Join issue on a maximum value
Re: Join issue on a maximum value Re: Join issue on a maximum value |
List | pgsql-sql |
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. I have 2 tables, auction and image, defined like this: Table "public.auction" Column | Type | Modifiers -----------------+---------+-----------------------------------------------------------------auction_id | integer |not null default nextval('public.auction_auction_id_seq'::text)auction_descrip | text |auction_owner | text | Indexes: "auction_pkey" primary key, btree (auction_id) Table "public.image" Column | Type | Modifiers -------------+---------+-------------------------------------------------------------image_id | integer | not null default nextval('public.image_image_id_seq'::text)auction_id | integer | not nullimage_descr | text | Indexes: "image_pkey" primary key, btree (image_id) Foreign-key constraints: "$1" FOREIGN KEY (auction_id) REFERENCES auction(auction_id) ON UPDATE RESTRICT ON DELETE RESTRICT Current data in the tables: play=# select * from auction play-# ;auction_id | auction_descrip | auction_owner ------------+-----------------+--------------- 1 | Mabel Auction 1 | Mabel 2 | Mabel Auction 2 | Mabel 3 | Mabel Auction 3 | Mabel 4 | Fred Auction 1 | Fred 5 | Fred Auction 2 | Fred play=# select * from image;image_id | auction_id | image_descr ----------+------------+------------- 1 | 1 | image 1 2 | 1 | image 2 3 | 2| image 3 4 | 3 | image 4 5 | 3 | image 5 6 | 4 | image 7 7 | 3 | image 8 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! Thanks, -Heflin