[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

Re: [SQL] Join issue on a maximum value

[Heflin]

Bruno Wolff III wrote:

On Wed, Apr 21, 2004 at 14:29:34 -0400,
 Heflin <[EMAIL PROTECTED]> wrote:
 

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';
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:
   

The postgres specific way of doing this is:
SELECT DISTINCT ON (auction.auction_id)
   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'
 ORDER BY auction.auction_id, image.image_id DESC
;
The more standard way to do it would be joining auction and image
with a group by and max to get the highest image_id and then joining
that result to image again to get the corresponding description.
 

Thanks!

I was actually trying to do it the more standard way, but I've been 
bungling up the syntax. I'm going to play with that some more, since it 
might be useful elsewhere.

The thing that disturbs me about your syntax is that I don't really see 
an assurance that I'll get the correct  image_id. Any chance you can 
tell me why this works?

Thanks again,

-Heflin




smime.p7s
Description: S/MIME Cryptographic Signature