Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.2/8.9.1) with ESMTP id KAA91659 for ; Tue, 2 Feb 1999 10:38:32 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id QAA02326; Tue, 2 Feb 1999 16:38:03 +0100 (MET) Received: from rosso.sferacarta.com (bo.nettuno.it) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 107iec-0007yA-00; Tue, 2 Feb 1999 16:26:22 +0000 Message-ID: <36B718DF.53BE37EF@bo.nettuno.it> Date: Tue, 02 Feb 1999 16:25:19 +0100 From: "jose' soares" Organization: Sferacarta Software X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Oliver Elphick CC: Tom Lane , pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org Subject: Re: [SQL] Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ... References: <199902011640.QAA20774@linda.lfix.co.uk> Content-Type: multipart/alternative; boundary="------------89937B63D72C8EE1042C7A28" --------------89937B63D72C8EE1042C7A28 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Oliver Elphick ha scritto: > "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 This is very interesting and useful, I thought it wasn't possible. Seems that standard allows only the "order by" column(s) and the aggregate function(s) on target list. I tried the same query on Informix, also on Ocelot but it gives me an error. On Informix and Ocelot queries like: select cognome, max(age) from membri where cap = '41010' group by cognome; are allowed. but queries like: select cognome, nome, via from membri where cap = '41010' group by cognome; aren't allowed. -Jose'- --------------89937B63D72C8EE1042C7A28 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  

Oliver Elphick ha scritto:

"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

This is very interesting and useful, I thought it wasn't possible. Seems that standard allows only the "order by" column(s)
and the aggregate function(s) on target list.
I tried the same query on Informix, also on Ocelot but it gives me an error.

On Informix and Ocelot
queries like:
   select  cognome, max(age) from membri where cap = '41010' group by cognome;
are allowed.
but
queries like:
   select cognome, nome, via from membri where cap = '41010' group by cognome;
aren't allowed.

-Jose'-
  --------------89937B63D72C8EE1042C7A28--