Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.1) with ESMTP id GAA46264 for ; Thu, 29 Apr 1999 06:29:20 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id D721C4BBFD; Thu, 29 Apr 1999 13:28:13 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA6FE2; Thu, 29 Apr 1999 13:30:21 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <21889.924884968@sss.pgh.pa.us> References: Your message of Fri, 23 Apr 1999 08:42:55 -0600 (MDT) <199904231442.IAA17294@trillium.nmsu.edu> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Thu, 29 Apr 1999 13:28:38 +0300 To: Tom Lane From: Herouth Maoz Subject: Re: [SQL] Finding the "most recent" rows Cc: pgsql-sql@postgreSQL.org At 19:29 +0300 on 23/04/1999, Tom Lane wrote: > I recall now that when I first heard of "SELECT DISTINCT ON field" > I objected that the results weren't well-defined (since it's not clear > how DISTINCT will choose which tuple to return). It might be that the > SQL spec requires the first tuple to be chosen for each value of > "field", which would allow the user to control the results by inserting > a preceding ORDER BY step --- or skip the ORDER BY, if he doesn't really > care which tuple he gets. That'd actually be a pretty cool design. > Anyone have an SQL spec handy to check it? DISTINCT ON is a non-standard structure. Your initial assertion, that it will return different results in different implementations holds true. Standard SQL should return the same result sets (order not important unless ORDER BY was mentioned). Tables are considered as unordered sets. There can not be anything in the standard that relies on the order in the table. And what do you mean by inserting a preceding ORDER BY step? Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma