Message-ID: <4086C9D2.9030707@onesystem.com>
Date: Wed, 21 Apr 2004 13:21:54 -0600
From: Edmund Bacon <ebacon@onesystem.com>
User-Agent: Mozilla Thunderbird 0.5 (X11/20040208)
MIME-Version: 1.0
To: Heflin <hhogan@tampabay.rr.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Join issue on a maximum value
References: <4086BD8E.90900@tampabay.rr.com>
In-Reply-To: <4086BD8E.90900@tampabay.rr.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Two choices that work:

Either add another JOIN in which retrieves the MAX(image_id) for each 
auction:

SELECT auction.auction_id, image.image_id, image.image_descr
    FROM auction
    JOIN image USING(auction_id)
    JOIN ( SELECT auction_id, MAX(image_id) AS image_id
             FROM image
            GROUP BY auction_id) max_aid USING (image_id)
    WHERE owner = 'Mabel'
    ORDER by auction.auction_id;

OR use a sub-select:

SELECT auction.auction_id, image_id, image.image_descr
   FROM auction
   JOIN image USING (auction_id)
  WHERE image_id = ( SELECT max(image_id) 
                       FROM image
                      WHERE auction_id = auction.auction_id)
     AND image_owner = 'Mabel';

Test both with your data - My experience is that the sub-select runs 
slower than throwing in the extra join.


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.
>
> 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
>
>
>

-- 
Edmund Bacon <ebacon@onesystem.com>