Re: [SQL] Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...

"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
(3 rows)

The particular values returned for nome and via are different from yours
but the same as I get using DISTINCT ON.  Since nome and via are not
aggregated, the value returned for those columns is unpredictable and
therefore not useful.  I think that it is actually a bug that you are
able to name them at all.

In fact, if you add an aggregate column to the column list, GROUP BY does
not then allow columns that are neither grouped nor aggregated:

junk=> select  cognome, nome,via, max(age) from membri where cap = '41010' group by cognome;
ERROR:  parser: illegal use of aggregates or non-group column in target list
junk=> select  cognome, max(age) from membri where cap = '41010' group by cognome;
cognome|max
-------+---
FIORANI| 54
GOZZI  | 76
RUSSO  | 45
(3 rows)

which definitely suggests that it is a bug to allow such fields when no
aggregate is specified.

DISTINCT ON fails with an aggregate, even if no other columns are named:

junk=> select distinct on cognome cognome, max(age) from membri where cap = '41010';
ERROR:  parser: illegal use of aggregates or non-group column in target list

which makes it even less useful!

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "And be not conformed to this world; but be ye
      transformed by the renewing of your mind, that ye may
      prove what is that good, and acceptable, and perfect,
      will of God."             Romans 12:2