From: Oliver Elphick <olly@lfix.co.uk>
To: jose' soares <sferac@bo.nettuno.it>
Cc: 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 16:40:37 +0000
Message-ID: <199902011640.QAA20774@linda.lfix.co.uk> (raw)
In-Reply-To: <sferac@bo.nettuno.it> <36B5C0F0.F331DAD1@bo.nettuno.it>
"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
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: olly@lfix.co.uk, sferac@bo.nettuno.it, 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: <199902011640.QAA20774@linda.lfix.co.uk>
References: <199902011640.QAA20774@linda.lfix.co.uk>
* 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