Received: from mail.enterprise.net (root@mail.enterprise.net [194.72.192.18]) by hub.org (8.9.2/8.9.1) with ESMTP id NAA33291 for ; Fri, 29 Jan 1999 13:24:16 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@ppp137.enterprise.net [194.72.195.137]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id SAA02213; Fri, 29 Jan 1999 18:24:12 GMT Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.2/8.9.1/Debian/GNU) with ESMTP id SAA24917; Fri, 29 Jan 1999 18:24:03 GMT Message-Id: <199901291824.SAA24917@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: Tom Lane cc: pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org Subject: Re: [HACKERS] Re: SELECT DISTINCT ON ... ORDER BY ... In-Reply-To: Message from Tom Lane of "Fri, 29 Jan 1999 12:02:52 EST." <25791.917629372@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Fri, 29 Jan 1999 18:24:03 +0000 From: "Oliver Elphick" Tom Lane wrote: >Thomas Metz 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