Skip site navigation (1) Skip section navigation (2)

Join issue on a maximum value

From: Heflin <hhogan(at)tampabay(dot)rr(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Join issue on a maximum value
Date: 2004-04-21 18:29:34
Message-ID: 4086BD8E.90900@tampabay.rr.com (view raw or flat)
Thread:
Lists: 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 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



Responses

pgsql-sql by date

Next:From: Bruno Wolff IIIDate: 2004-04-21 18:51:22
Subject: Re: Join issue on a maximum value
Previous:From: Bruno Wolff IIIDate: 2004-04-21 18:02:42
Subject: Re: transaction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group