From: Heflin <hhogan@tampabay.rr.com>
To: pgsql-sql@postgresql.org
Subject: Join issue on a maximum value
Date: Wed, 21 Apr 2004 14:29:34 -0400
Message-ID: <4086BD8E.90900@tampabay.rr.com> (raw)
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
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.3K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hhogan@tampabay.rr.com
Subject: Re: Join issue on a maximum value
In-Reply-To: <4086BD8E.90900@tampabay.rr.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox