pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jose' soares <sferac@bo.nettuno.it>
To: 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: Tue, 02 Feb 1999 16:25:19 +0100
Message-ID: <36B718DF.53BE37EF@bo.nettuno.it> (raw)
References: <199902011640.QAA20774@linda.lfix.co.uk>



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

view thread (9+ messages)  latest in thread

Message-ID: <36B718DF.53BE37EF@bo.nettuno.it>
Permalink:  ../36B718DF.53BE37EF@bo.nettuno.it/
Also on:    postgresql.org/message-id/36B718DF.53BE37EF@bo.nettuno.it

 · 

reply

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: sferac@bo.nettuno.it, olly@lfix.co.uk, 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: <36B718DF.53BE37EF@bo.nettuno.it>
  References: <199902011640.QAA20774@linda.lfix.co.uk> <36B718DF.53BE37EF@bo.nettuno.it>

* 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