Re: Join issue on a maximum value

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(at)onesystem(dot)com>