X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4E8B0D1DB4C for ; Wed, 21 Apr 2004 15:29:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 78791-03 for ; Wed, 21 Apr 2004 15:29:42 -0300 (ADT) Received: from ms-smtp-02.tampabay.rr.com (ms-smtp-02-smtplb.tampabay.rr.com [65.32.5.132]) by svr1.postgresql.org (Postfix) with ESMTP id 439C1D1DB0D for ; Wed, 21 Apr 2004 15:29:40 -0300 (ADT) Received: from tampabay.rr.com (653224hfc131.tampabay.rr.com [65.32.24.131]) by ms-smtp-02.tampabay.rr.com (8.12.10/8.12.7) with ESMTP id i3LITc83003399 for ; Wed, 21 Apr 2004 14:29:39 -0400 (EDT) Message-ID: <4086BD8E.90900@tampabay.rr.com> Date: Wed, 21 Apr 2004 14:29:34 -0400 From: Heflin User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 Netscape/6.2 X-Accept-Language: en-us, en, ar-bh, zh-cn, zh-hk, zh-tw, en-ie, ga, ja, ru MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Join issue on a maximum value Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms070001080009060302030003" X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/210 X-Sequence-Number: 17585 This is a cryptographically signed message in MIME format. --------------ms070001080009060302030003 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------ms070001080009060302030003 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIJkDCCBMQwggKsoAMCAQICAg96MA0GCSqGSIb3DQEBBAUAMHkxEDAO BgNVBAoTB1Jvb3QgQ0ExHjAcBgNVBAsTFWh0dHA6Ly93d3cuY2FjZXJ0Lm9y ZzEiMCAGA1UEAxMZQ0EgQ2VydCBTaWduaW5nIEF1dGhvcml0eTEhMB8GCSqG SIb3DQEJARYSc3VwcG9ydEBjYWNlcnQub3JnMB4XDTAzMTIyMjE5MDQwMVoX DTA0MTIyMTE5MDQwMVowQjEZMBcGA1UEAxMQQ0FjZXJ0IFVzZXIgQ2VydDEl MCMGCSqGSIb3DQEJARYWaGhvZ2FuQHRhbXBhYmF5LnJyLmNvbTCCASIwDQYJ KoZIhvcNAQEBBQADggEPADCCAQoCggEBAK/1CWtlyvAJDFkziyBRAV4VvmU6 jtvKlJez4VW9sRa8/D57C3hMxjpVKGtmVZ/It5KHbrYS4e+H8nSA7nd6pB4l y7uAJ2K99FfBpkzWvO6VYfF/f7mnocmtOgzz0P7GE0BuXDUN5bkootPp0CI7 EM1MI5jlKeTZxi4HWIoZS4Z/655RpgEqxRDo/P+TKYku0nkNvNjsgFXZMTS/ oXbCtiryjoKKDscG/rqdbiGJ42PQCnguW1OAGGCeVROHc+Xz/NaRpOVgZTqj uz1se92PLC4OEULzxC332UXotzcDZaNPoEJ0Fii6phDaGnBzvXRS/lrWGeuR 2aD/ManTjBMESkkCAwEAAaOBjDCBiTAMBgNVHRMBAf8EAjAAMFYGCWCGSAGG +EIBDQRJFkdUbyBnZXQgeW91ciBvd24gY2VydGlmaWNhdGUgZm9yIEZSRUUg aGVhZCBvdmVyIHRvIGh0dHA6Ly93d3cuY2FjZXJ0Lm9yZzAhBgNVHREEGjAY gRZoaG9nYW5AdGFtcGFiYXkucnIuY29tMA0GCSqGSIb3DQEBBAUAA4ICAQBG bX9DNO965BA4/vbkEHN5q9RqUlQCyd3CYOZSFVW1gizc8B4+RWlChP8pZ4F+ +Ro8v2ZhYjGdaOAlxn8EGbYgEbPZJ06tXEONIJiZNehCibG0pYGwuR0mDmpo jhOGeF6Dyf9iWEv9DXxcb4DRGpCx8kbNuXUrNO1P0/xLf/HXHGyP1uBuPKgO JCE39VzgBAusN4WXXyN8qZHuu3TtraesRktgRl8CuT1almE2pviVtoA2lNfu t5b2qh78z7GExTleYhLbIyYQF6LDhox7VWHC+UiUGMurkWAhW4FXPLo9BJDm baRPpE3BcKxpaNnUZOSfWi6BRpjMyDOdOihxLGenHfSlneT7Vz55ZKKUh1Sy Vjnfh8cTS3RSE33yd7T//rx0hDsDLXlyDVotP53GfE+oblzG3NoE4iJVr7rU ITjNtnunPyv5TmXUGkJ3b6KLlFXaI7Sk+4krz/G7CDrCVxqbZo3lQAqjFSoU nveKuglvSt1HFPHzTy6GuivvWayatgq31bUsB27OQA9PEOJSbVBFd9TaHbTR VjK14Be84IZBcz2Xmu6Fo8WDoSZma84lM8CZrBqMHyXU/UzHwFyy5XdiEeM4 KZrTbdaGZ98lJlmB5CNq6kfyMR4IRyA3s1qxPPOLmFPmrAvkBJjZLxLskOo/ I7SrZdydMTDhFOFgD74yETCCBMQwggKsoAMCAQICAg96MA0GCSqGSIb3DQEB BAUAMHkxEDAOBgNVBAoTB1Jvb3QgQ0ExHjAcBgNVBAsTFWh0dHA6Ly93d3cu Y2FjZXJ0Lm9yZzEiMCAGA1UEAxMZQ0EgQ2VydCBTaWduaW5nIEF1dGhvcml0 eTEhMB8GCSqGSIb3DQEJARYSc3VwcG9ydEBjYWNlcnQub3JnMB4XDTAzMTIy MjE5MDQwMVoXDTA0MTIyMTE5MDQwMVowQjEZMBcGA1UEAxMQQ0FjZXJ0IFVz ZXIgQ2VydDElMCMGCSqGSIb3DQEJARYWaGhvZ2FuQHRhbXBhYmF5LnJyLmNv bTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAK/1CWtlyvAJDFkz iyBRAV4VvmU6jtvKlJez4VW9sRa8/D57C3hMxjpVKGtmVZ/It5KHbrYS4e+H 8nSA7nd6pB4ly7uAJ2K99FfBpkzWvO6VYfF/f7mnocmtOgzz0P7GE0BuXDUN 5bkootPp0CI7EM1MI5jlKeTZxi4HWIoZS4Z/655RpgEqxRDo/P+TKYku0nkN vNjsgFXZMTS/oXbCtiryjoKKDscG/rqdbiGJ42PQCnguW1OAGGCeVROHc+Xz /NaRpOVgZTqjuz1se92PLC4OEULzxC332UXotzcDZaNPoEJ0Fii6phDaGnBz vXRS/lrWGeuR2aD/ManTjBMESkkCAwEAAaOBjDCBiTAMBgNVHRMBAf8EAjAA MFYGCWCGSAGG+EIBDQRJFkdUbyBnZXQgeW91ciBvd24gY2VydGlmaWNhdGUg Zm9yIEZSRUUgaGVhZCBvdmVyIHRvIGh0dHA6Ly93d3cuY2FjZXJ0Lm9yZzAh BgNVHREEGjAYgRZoaG9nYW5AdGFtcGFiYXkucnIuY29tMA0GCSqGSIb3DQEB BAUAA4ICAQBGbX9DNO965BA4/vbkEHN5q9RqUlQCyd3CYOZSFVW1gizc8B4+ RWlChP8pZ4F++Ro8v2ZhYjGdaOAlxn8EGbYgEbPZJ06tXEONIJiZNehCibG0 pYGwuR0mDmpojhOGeF6Dyf9iWEv9DXxcb4DRGpCx8kbNuXUrNO1P0/xLf/HX HGyP1uBuPKgOJCE39VzgBAusN4WXXyN8qZHuu3TtraesRktgRl8CuT1almE2 pviVtoA2lNfut5b2qh78z7GExTleYhLbIyYQF6LDhox7VWHC+UiUGMurkWAh W4FXPLo9BJDmbaRPpE3BcKxpaNnUZOSfWi6BRpjMyDOdOihxLGenHfSlneT7 Vz55ZKKUh1SyVjnfh8cTS3RSE33yd7T//rx0hDsDLXlyDVotP53GfE+oblzG 3NoE4iJVr7rUITjNtnunPyv5TmXUGkJ3b6KLlFXaI7Sk+4krz/G7CDrCVxqb Zo3lQAqjFSoUnveKuglvSt1HFPHzTy6GuivvWayatgq31bUsB27OQA9PEOJS bVBFd9TaHbTRVjK14Be84IZBcz2Xmu6Fo8WDoSZma84lM8CZrBqMHyXU/UzH wFyy5XdiEeM4KZrTbdaGZ98lJlmB5CNq6kfyMR4IRyA3s1qxPPOLmFPmrAvk BJjZLxLskOo/I7SrZdydMTDhFOFgD74yETGCA4EwggN9AgEBMH8weTEQMA4G A1UEChMHUm9vdCBDQTEeMBwGA1UECxMVaHR0cDovL3d3dy5jYWNlcnQub3Jn MSIwIAYDVQQDExlDQSBDZXJ0IFNpZ25pbmcgQXV0aG9yaXR5MSEwHwYJKoZI hvcNAQkBFhJzdXBwb3J0QGNhY2VydC5vcmcCAg96MAkGBSsOAwIaBQCgggHX MBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTA0 MDQyMTE4MjkzNFowIwYJKoZIhvcNAQkEMRYEFBVGh02itrCTpM/cJeUlrMje IaOuMFIGCSqGSIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwIC AgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGP BgkrBgEEAYI3EAQxgYEwfzB5MRAwDgYDVQQKEwdSb290IENBMR4wHAYDVQQL ExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNBIENlcnQgU2ln bmluZyBBdXRob3JpdHkxITAfBgkqhkiG9w0BCQEWEnN1cHBvcnRAY2FjZXJ0 Lm9yZwICD3owgZEGCyqGSIb3DQEJEAILMYGBoH8weTEQMA4GA1UEChMHUm9v dCBDQTEeMBwGA1UECxMVaHR0cDovL3d3dy5jYWNlcnQub3JnMSIwIAYDVQQD ExlDQSBDZXJ0IFNpZ25pbmcgQXV0aG9yaXR5MSEwHwYJKoZIhvcNAQkBFhJz dXBwb3J0QGNhY2VydC5vcmcCAg96MA0GCSqGSIb3DQEBAQUABIIBAKiLhCvW kGwwts2BLChRVQ903mG1dE+v6yoSuPRhSCSn5bRtVmMEsaVJHKmbDgZLBgZw SO1yEkXKM7ORhWUI6Pjtko0RXAlJl98Mwk8RLk57mrc0lGBFmBCUDedurb2C Rz/5ytijdjwMmb5k3xP03lcrpnd4sk2H1NONnj7bGaKVMUOplyoEMmqkxNBY 3EDkCgLk7rZ92k1f4VJhYiytXsOLsL9C4PyQEOf4KUhHB8/+Crw/JBeMLkUw WnlniAYkNSQ8HoQ62KqGK+fj/Cd5XACMsfdowjYTfn93RwpOtIqzQyBoJ1su jNIWlrCcN2JSJ3N2nRCuqBDDpm21J/NvThgAAAAAAAA= --------------ms070001080009060302030003--