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 7FA58D1D0B8 for ; Wed, 21 Apr 2004 17:28:29 -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 20643-08 for ; Wed, 21 Apr 2004 17:28:24 -0300 (ADT) Received: from ms-smtp-03.tampabay.rr.com (ms-smtp-03-smtplb.tampabay.rr.com [65.32.5.133]) by svr1.postgresql.org (Postfix) with ESMTP id 6DA88D1C4E0 for ; Wed, 21 Apr 2004 17:28:22 -0300 (ADT) Received: from tampabay.rr.com (653224hfc131.tampabay.rr.com [65.32.24.131]) by ms-smtp-03.tampabay.rr.com (8.12.10/8.12.7) with ESMTP id i3LKSEuJ012620; Wed, 21 Apr 2004 16:28:17 -0400 (EDT) Message-ID: <4086D95A.5000609@tampabay.rr.com> Date: Wed, 21 Apr 2004 16:28:10 -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: Bruno Wolff III Cc: pgsql-sql@postgresql.org Subject: Re: Join issue on a maximum value References: <4086BD8E.90900@tampabay.rr.com> <20040421185122.GA31195@wolff.to> In-Reply-To: <20040421185122.GA31195@wolff.to> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms090307080702060508030300" 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/215 X-Sequence-Number: 17590 This is a cryptographically signed message in MIME format. --------------ms090307080702060508030300 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Bruno Wolff III wrote: >On Wed, Apr 21, 2004 at 14:29:34 -0400, > Heflin 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 --------------ms090307080702060508030300 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 MDQyMTIwMjgxMFowIwYJKoZIhvcNAQkEMRYEFOxfMoPXqSov5hl6DdxA51ds UJE0MFIGCSqGSIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwIC AgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGP BgkrBgEEAYI3EAQxgYEwfzB5MRAwDgYDVQQKEwdSb290IENBMR4wHAYDVQQL ExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNBIENlcnQgU2ln bmluZyBBdXRob3JpdHkxITAfBgkqhkiG9w0BCQEWEnN1cHBvcnRAY2FjZXJ0 Lm9yZwICD3owgZEGCyqGSIb3DQEJEAILMYGBoH8weTEQMA4GA1UEChMHUm9v dCBDQTEeMBwGA1UECxMVaHR0cDovL3d3dy5jYWNlcnQub3JnMSIwIAYDVQQD ExlDQSBDZXJ0IFNpZ25pbmcgQXV0aG9yaXR5MSEwHwYJKoZIhvcNAQkBFhJz dXBwb3J0QGNhY2VydC5vcmcCAg96MA0GCSqGSIb3DQEBAQUABIIBAFd8SpPd wIVinQToD5GB/RL7z8lk0N4k349qP3uyufzlp7a0fWfjEgC7YBa5VVZm0hpU 3T0a0enbDZqgv9pKooJ575IIjMkKx6u/T/r1gpCWSAZh7A39jXBsTJuby8ho sRqfjeqwpnixBSHOEbNOjNFtG5szHtOOgwaGbu+ezZwbwcKD9d7uaZgjyG+S UUXr2Nvf+35MrsfO5DRJQRFns82P1K8XQmSSytlD9RaJHd2ZuHS7AcJ4qidZ Zx8h4Hwb7Kjd9JAmhy95kqeYor56W+kpwwAqxsretRYanqY9ybZyqbFxA0Li sb50mk+rP75jb812IrVhEjep5OOuTfENSpUAAAAAAAA= --------------ms090307080702060508030300--