Received: from mail.enterprise.net (root@mail.enterprise.net [194.72.192.18]) by hub.org (8.9.2/8.9.1) with ESMTP id LAA25335 for ; Mon, 1 Feb 1999 11:41:03 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@ppp463.enterprise.net [194.72.196.209]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id QAA02040; Mon, 1 Feb 1999 16:40:54 GMT Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.2/8.9.1/Debian/GNU) with ESMTP id QAA20774; Mon, 1 Feb 1999 16:40:38 GMT Message-Id: <199902011640.QAA20774@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: "jose' soares" cc: Oliver Elphick , Tom Lane , pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org Subject: Re: [SQL] Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ... In-Reply-To: Message from "jose' soares" of "Mon, 01 Feb 1999 15:57:52 +0100." <36B5C0F0.F331DAD1@bo.nettuno.it> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Mon, 01 Feb 1999 16:40:37 +0000 From: "Oliver Elphick" "jose' soares" wrote: >Yes, seems that SELECT DISTINCT ON is not part of SQL92 but it is very >interesting and I think it is something missing to Standard. >I don't know how to do the following, if we take off DISTINCT ON from >PostgreSQL: > >db=> select distinct cognome, nome,via from membri where cap = '41010'; >cognome|nome |via >-------+----------------+-------------------------- >FIORANI|ELISABETTA |VIA PRETI PARTIGIANI, 63 >FIORANI|GASTONE |VIA PRETI PARTIGIANI, 63 >FIORANI|MATTIA |VIA PRETI PARTIGIANI, 63 >FIORANI|SIMONE |VIA PRETI PARTIGIANI, 63 >GOZZI |LILIANA |VIA MAGNAGHI, 39 >GOZZI |MATTEO |VIA MAGNAGHI, 39 >RUSSO |DAVIDE |STRADA CORLETTO SUD, 194/1 >RUSSO |ELENA TERESA |STRADA CORLETTO SUD, 194/1 >RUSSO |FORTUNATO |STRADA CORLETTO SUD, 194/1 >RUSSO |MAURIZIO ANTONIO|STRADA CORLETTO SUD, 194/1 >(10 rows) > >db=> select distinct on cognome cognome, nome,via from membri where cap = >'41010'; >cognome|nome |via >-------+----------------+-------------------------- >FIORANI|GASTONE |VIA PRETI PARTIGIANI, 63 >GOZZI |LILIANA |VIA MAGNAGHI, 39 >RUSSO |MAURIZIO ANTONIO|STRADA CORLETTO SUD, 194/1 >(3 rows) This gives the same results: junk=> select cognome, nome, via from membri where cap = '41010' group by cognome; cognome|nome |via -------+----------+-------------------------- FIORANI|ELISABETTA|VIA PRETI PARTIGIANI, 63 GOZZI |LILIANA |VIA MAGNAGHI, 39 RUSSO |DAVIDE |STRADA CORLETTO SUD, 194/1 (3 rows) The particular values returned for nome and via are different from yours but the same as I get using DISTINCT ON. Since nome and via are not aggregated, the value returned for those columns is unpredictable and therefore not useful. I think that it is actually a bug that you are able to name them at all. In fact, if you add an aggregate column to the column list, GROUP BY does not then allow columns that are neither grouped nor aggregated: junk=> select cognome, nome,via, max(age) from membri where cap = '41010' group by cognome; ERROR: parser: illegal use of aggregates or non-group column in target list junk=> select cognome, max(age) from membri where cap = '41010' group by cognome; cognome|max -------+--- FIORANI| 54 GOZZI | 76 RUSSO | 45 (3 rows) which definitely suggests that it is a bug to allow such fields when no aggregate is specified. DISTINCT ON fails with an aggregate, even if no other columns are named: junk=> select distinct on cognome cognome, max(age) from membri where cap = '41010'; ERROR: parser: illegal use of aggregates or non-group column in target list which makes it even less useful! -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "And be not conformed to this world; but be ye transformed by the renewing of your mind, that ye may prove what is that good, and acceptable, and perfect, will of God." Romans 12:2