Thread

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

Oliver Elphick <olly@lfix.co.uk> — 1999-02-01T16:40:37Z

"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

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

jose' soares <sferac@bo.nettuno.it> — 1999-02-02T15:25:19Z


Oliver Elphick ha scritto:

> "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

This is very interesting and useful, I thought it wasn't possible. Seems that standard allows
only the "order by" column(s)
and the aggregate function(s) on target list.
I tried the same query on Informix, also on Ocelot but it gives me an error.

On Informix and Ocelot
queries like:
   select  cognome, max(age) from membri where cap = '41010' group by cognome;
are allowed.
but
queries like:
   select cognome, nome, via from membri where cap = '41010' group by cognome;
aren't allowed.

-Jose'-

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

Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-02-02T15:57:08Z

At 17:25 +0200 on 02/02/1999, jose' soares wrote:

> 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
>
>  This is very interesting and useful, I thought it wasn't possible. Seems
>that standard allows only the "order by" column(s)
> and the aggregate function(s) on target list.
> I tried the same query on Informix, also on Ocelot but it gives me an error.

And with good reason, too. The above query has the same drawback as the
"select distinct on", which is: it does not fully specify which value
should be selected for the "nome" and "via" fields.

Thus, running this same query on a table that has the same data but was,
for example, filled in a different order, gives a different result. That's
bad, because order should not make a difference for output. Tables are
taken to be unordered sets.

If you want to have a representative of the "nome" and "via" fields, and it
doesn't matter which representative, then min(nome) or max(nome) should do
the trick. And this query (select cognome, min(nome), min(via)... group by
cognome) should give you the same result on all databases, no matter which
rows were inserted first.

If it was up to me, I wouldn't use the above form, and frankly, I am
surprised the Postgres allows this.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma