[SQL] Join issue on a maximum value

[Heflin]

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 null
image_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

smime.p7s

Description: S/MIME Cryptographic Signature