Received: from intragate.popso.it (intergate.popso.it [194.243.98.135]) by hub.org (8.9.3/8.9.3) with SMTP id KAA51963 for ; Wed, 25 Aug 1999 10:13:03 -0400 (EDT) (envelope-from Giampiero.Raschetti@popso.it) Received: tid QAA05979; Wed, 25 Aug 1999 16:09:40 +0200 Received: (from mail@localhost) by mail.popso.it (8.8.7/8.8.7) id QAA06567; Wed, 25 Aug 1999 16:06:17 +0200 Received: from internal host by mail.popso.it via smap (personal) id xma006561; Wed, 25 Aug 99 16:06:08 +0200 Sender: jimi@popso.it Message-ID: <37C3F9C8.BB2E42DF@popso.it> Date: Wed, 25 Aug 1999 16:12:24 +0200 From: Giampiero Raschetti X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.5-15 i586) X-Accept-Language: en MIME-Version: 1.0 To: Herouth Maoz CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] PgSQL 6.5.1 and Group BY pb References: Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms1E0AA340F876BDA1660A93E6" This is a cryptographically signed message in MIME format. --------------ms1E0AA340F876BDA1660A93E6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Herouth Maoz wrote: > > At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote: > > > ............................... > > I think you have a misunderstanding about the purpose of group by statements. > > In fact, the above seems to indicate that you are not well aware of what > joins are, or at least you don't know that you have to restrict them to > make sense of your data. You really have to add WHERE g.id = u.id. > OK. That's true. I'm not well aware of SQL in general but if you try out that secuence into postgres 6.4.2 you will see that it works. So I understand that probably it was not standard. Well. Let's take a look at this same problem without any join involved and if possible let me know how to obtain my target: xaxa=> \d comuni Table = comuni +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | produtt | varchar() | 40 | | unital | varchar() | 40 | | comune | varchar() | 40 | | login | varchar() | 8 | +----------------------------------+----------------------------------+-------+ xaxa=> select * from comuni; produtt| unital|comune |login -------+---------------+------------+----- xxx |199900200000100|POGGIRIDENTI|user1 yyy |199900200000100|POGGIRIDENTI|user2 yyy |199900200000100|ARDENNO |USER3 yyy |199900200000100|BORMIO |user2 (4 rows) xaxa=> select * from comuni group by login; ERROR: Illegal use of aggregates or non-group column in target list xaxa=> This works too on Postgres 6.4.2 so how can I obtain a list of unique users from this table on 6.5.1 ? Many Thanks -- Best Regards ---------------------------- Giampiero Raschetti Sistemi Innovativi Banca Popolare di Sondrio ---------------------------- --------------ms1E0AA340F876BDA1660A93E6 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 MIIHrwYJKoZIhvcNAQcCoIIHoDCCB5wCAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCC BdEwggKQMIIB+aADAgECAgMAm8UwDQYJKoZIhvcNAQEEBQAwgbkxCzAJBgNVBAYTAlpBMRUw EwYDVQQIEwxXZXN0ZXJuIENhcGUxFDASBgNVBAcTC0R1cmJhbnZpbGxlMRowGAYDVQQKExFU aGF3dGUgQ29uc3VsdGluZzEpMCcGA1UECxMgVGhhd3RlIFBGIFJTQSBJSyAxOTk4LjkuMTYg MTc6NTUxNjA0BgNVBAMTLVRoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBSU0EgSXNzdWVyIDE5 OTguOS4xNjAeFw05OTAzMDgxNDAzNTZaFw0wMDAzMDcxNDAzNTZaME4xHzAdBgNVBAMTFlRo YXd0ZSBGcmVlbWFpbCBNZW1iZXIxKzApBgkqhkiG9w0BCQEWHGdpYW1waWVyby5yYXNjaGV0 dGlAcG9wc28uaXQwXDANBgkqhkiG9w0BAQEFAANLADBIAkEApIaNCBaZMD9TL8Shxj0vkHxY dtEmNpPEWFun75AdRJ6/Jxp1bhV5HO02UwMUrzwmlFBSKgU02OFRxXtNDxGA8wIDAQABo1Qw UjARBglghkgBhvhCAQEEBAMCBaAwDgYDVR0PAQH/BAQDAgWgMAwGA1UdEwEB/wQCMAAwHwYD VR0jBBgwFoAU/j5gnGuMD7DYM8bKxh5YsHE4teAwDQYJKoZIhvcNAQEEBQADgYEAn6o/B6kK 93HPOUKd3M7qV9TVhT9fq0SKXN79f5bWWgKN3rbFfVSlx0dc4Tc14yT+45xM9t2jelUGBavV f4aEfFqb4uvFIS8v6GS4tjEY3+QvltiDvTvSRyfwDWZF6otsodVKyLv9CVVs0e8e9An2jp1Z Nk+IVwi0k/6ZQoqwSRUwggM5MIICoqADAgECAgEKMA0GCSqGSIb3DQEBBAUAMIHRMQswCQYD VQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAY BgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZp Y2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzAp BgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNOTgwOTE2MTc1 NTM0WhcNMDAwOTE1MTc1NTM0WjCBuTELMAkGA1UEBhMCWkExFTATBgNVBAgTDFdlc3Rlcm4g Q2FwZTEUMBIGA1UEBxMLRHVyYmFudmlsbGUxGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5n MSkwJwYDVQQLEyBUaGF3dGUgUEYgUlNBIElLIDE5OTguOS4xNiAxNzo1NTE2MDQGA1UEAxMt VGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIFJTQSBJc3N1ZXIgMTk5OC45LjE2MIGfMA0GCSqG SIb3DQEBAQUAA4GNADCBiQKBgQDEpeXU1NBfCALuByF9JL+ra44e6yAHAhWEa4/QkyQfG53u aLK5LE/pk2cXEBceoflDQSO5MKp2l7vz5/2BwLUxi/amUCZU8pUo6xmkHpcesOK4m8EEmjLQ PAlsT+Q1T/B2vwATA09FCGDz/LTQkAGKEsmcun9S6iqTNTY8POQ1LwIDAQABozcwNTASBgNV HRMBAf8ECDAGAQH/AgEAMB8GA1UdIwQYMBaAFHJJwnM0xlX0C3ZygX539IfnxrIOMA0GCSqG SIb3DQEBBAUAA4GBACzHgh8BQz4Hj+5pXKlkgvjAlq2TK8ubUNdAmoHCuqZ2nTyVQNxVweFV gnmrCimm1QzhVyg+j/m71d8Nk1iqWy2LjzPk3VgVNXZyFSm9QvRakgt3X50n25otThuCBo7S jVa7ld7bDGUF3pWeAt1TF76+/GvDGiJ6FCthvcKfXnpaMYIBpjCCAaICAQEwgcEwgbkxCzAJ BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxFDASBgNVBAcTC0R1cmJhbnZpbGxl MRowGAYDVQQKExFUaGF3dGUgQ29uc3VsdGluZzEpMCcGA1UECxMgVGhhd3RlIFBGIFJTQSBJ SyAxOTk4LjkuMTYgMTc6NTUxNjA0BgNVBAMTLVRoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBS U0EgSXNzdWVyIDE5OTguOS4xNgIDAJvFMAkGBSsOAwIaBQCgfTAYBgkqhkiG9w0BCQMxCwYJ KoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw05OTA4MjUxNDEyMjZaMB4GCSqGSIb3DQEJDzER MA8wDQYIKoZIhvcNAwICASgwIwYJKoZIhvcNAQkEMRYEFE78LiOU9a5zyiyeOhUhIirdEewa MA0GCSqGSIb3DQEBAQUABEBeP9pRD0lcNY8ZR5uKtpOKTE48bSmawxm783sivMnBwAPZPkOh zcowtRMv8X4C5DxXJTvAfhFceojqQgYv/tlE --------------ms1E0AA340F876BDA1660A93E6--