From: jose' soares <sferac@bo.nettuno.it>
To: Oliver Elphick <olly@lfix.co.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [SQL] Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...
Date: Mon, 01 Feb 1999 15:57:52 +0100
Message-ID: <36B5C0F0.F331DAD1@bo.nettuno.it> (raw)
References: <199901291824.SAA24917@linda.lfix.co.uk>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: sferac@bo.nettuno.it, olly@lfix.co.uk, tgl@sss.pgh.pa.us, pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org
Subject: Re: [SQL] Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...
In-Reply-To: <36B5C0F0.F331DAD1@bo.nettuno.it>
References: <199901291824.SAA24917@linda.lfix.co.uk> <36B5C0F0.F331DAD1@bo.nettuno.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox