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  (Bruno Wolff III <bruno@wolff.to>)
Re: Join issue on a maximum value  (Edmund Bacon <ebacon@onesystem.com>)
Re: Join issue on a maximum value  (Jeremy Semeiks <jrs@denny.farviolet.com>)
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




pgsql-sql by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: transaction
Next
From: Bruno Wolff III
Date:
Subject: Re: Join issue on a maximum value