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)