Thread
-
Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...
Oliver Elphick <olly@lfix.co.uk> — 1999-01-29T18:24:03Z
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! -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "My son, if sinners entice thee, consent thou not." Proverbs 1:10 -
Re: [SQL] Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...
jose' soares <sferac@bo.nettuno.it> — 1999-02-01T14:57:52Z
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)