Re: Join issue on a maximum value - Mailing list pgsql-sql

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 (
SELECTauction_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 = (
SELECTmax(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>