pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oliver Elphick <olly@lfix.co.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...
Date: Fri, 29 Jan 1999 18:24:03 +0000
Message-ID: <199901291824.SAA24917@linda.lfix.co.uk> (raw)
In-Reply-To: <tgl@sss.pgh.pa.us> <25791.917629372@sss.pgh.pa.us>

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 





view thread (9+ messages)  latest in thread

Message-ID: <199901291824.SAA24917@linda.lfix.co.uk>
Permalink:  ../199901291824.SAA24917@linda.lfix.co.uk/
Also on:    postgresql.org/message-id/199901291824.SAA24917@linda.lfix.co.uk

 · 

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: olly@lfix.co.uk, tgl@sss.pgh.pa.us, pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org
  Subject: Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ...
  In-Reply-To: <199901291824.SAA24917@linda.lfix.co.uk>
  References: <199901291824.SAA24917@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