Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.2/8.9.1) with ESMTP id KAA10309 for ; Mon, 1 Feb 1999 10:01:41 -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 QAA17994; Mon, 1 Feb 1999 16:00:36 +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 107LkQ-0001mM-00; Mon, 1 Feb 1999 15:58:50 +0000 Message-ID: <36B5C0F0.F331DAD1@bo.nettuno.it> Date: Mon, 01 Feb 1999 15:57:52 +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: <199901291824.SAA24917@linda.lfix.co.uk> Content-Type: multipart/alternative; boundary="------------C5F9EBCCCEC875CD869BE0CE" --------------C5F9EBCCCEC875CD869BE0CE Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Oliver Elphick ha scritto: > Tom Lane wrote: > >Thomas Metz writes: > >> SELECT DISTINCT ON id id, name FROM test ORDER BY name; > >> [doesn't work as expected] > > > >There have been related discussions before on pg-hackers mail list; > >you might care to check the list archives. The conclusion I recall > >is that it's not real clear how the combination of SELECT DISTINCT > >on one column and ORDER BY on another *should* work. Postgres' > >current behavior is clearly wrong IMHO, but there isn't a unique > >definition of right behavior, because it's not clear which tuples > >should get selected for the sort. > > > >This "SELECT DISTINCT ON attribute" option strikes me as even more > >bogus. Where did we get that from --- is it in the SQL92 standard? > > I looked through the standard yesterday and couldn't find it. It doesn't > seem to be a useful extension, since it does nothing that you can't do > with GROUP BY and seems much less well defined. For the moment I have > added a brief description to the reference documentation for SELECT. > > >If you SELECT DISTINCT on a subset of the attributes to be returned, > >then there's no unique definition of which values get returned in the > >other columns. In Thomas' example: > ... > >Any of these are "DISTINCT ON id", but it's purely a matter of > >happenstance table order and unspecified implementation choices which > >one will appear. Do we really have (or want) a statement with > >inherently undefined behavior? > > We have it; I suggest we don't want it! 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) --------------C5F9EBCCCEC875CD869BE0CE Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  

Oliver Elphick ha scritto:

Tom Lane wrote:
  >Thomas Metz <tmetz@gsf.de> writes:
  >> SELECT DISTINCT ON id id, name FROM test ORDER BY name;
  >> [doesn't work as expected]
  >
  >There have been related discussions before on pg-hackers mail list;
  >you might care to check the list archives.  The conclusion I recall
  >is that it's not real clear how the combination of SELECT DISTINCT
  >on one column and ORDER BY on another *should* work.  Postgres'
  >current behavior is clearly wrong IMHO, but there isn't a unique
  >definition of right behavior, because it's not clear which tuples
  >should get selected for the sort.
  >
  >This "SELECT DISTINCT ON attribute" option strikes me as even more
  >bogus.  Where did we get that from --- is it in the SQL92 standard?

I looked through the standard yesterday and couldn't find it.  It doesn't
seem to be a useful extension, since it does nothing that you can't do
with GROUP BY and seems much less well defined.  For the moment I have
added a brief description to the reference documentation for SELECT.

  >If you SELECT DISTINCT on a subset of the attributes to be returned,
  >then there's no unique definition of which values get returned in the
  >other columns.  In Thomas' example:
 ...
  >Any of these are "DISTINCT ON id", but it's purely a matter of
  >happenstance table order and unspecified implementation choices which
  >one will appear.  Do we really have (or want) a statement with
  >inherently undefined behavior?

We have it; I suggest we don't want it!

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)
  --------------C5F9EBCCCEC875CD869BE0CE--