Received: from acheron.rime.com.au (root@albatr.lnk.telstra.net [139.130.54.222]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA64435; Mon, 24 Jan 2000 22:03:56 -0500 (EST) (envelope-from pjw@rhyme.com.au) Received: from oberon (Oberon.rime.com.au [203.8.195.100]) by acheron.rime.com.au (8.9.3/8.9.3) with SMTP id OAA05634; Tue, 25 Jan 2000 14:02:26 +1100 Message-Id: <3.0.5.32.20000125140349.035768d0@mail.rhyme.com.au> X-Sender: pjw@mail.rhyme.com.au X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.5 (32) Date: Tue, 25 Jan 2000 14:03:49 +1100 To: Chris Bitmead , pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org From: Philip Warner Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace In-Reply-To: <388D0D55.84F13203@bitmead.com> References: <2295.948765015@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" At 13:41 25/01/00 +1100, Chris Bitmead wrote: >Tom Lane wrote: >> >> If I don't hear loud hollers very soon, I'm going to eliminate the >> DISTINCT ON "feature" for 7.0. As previously discussed, this feature >> is not standard SQL and has no clear semantic interpretation. > >I don't feel overly strongly about this, but if I remember right you can >do some pretty cool things with this feature, provided you do define >some semantics clearly. Like I think you can find the first tuple >(given some ORDER BY clause) that fulfills some criteria. I think it is > >SELECT DISTINCT ON name name, age ORDER BY age; > >will get the youngest person. This might not be clearly specified now, >but >as long as it's useful, how about clearly defining it? I don't know that >there is an easy way of doing this in standard SQL. I don't know about PGSQL, but in other systems, I use: Select from order by age asc limit to 1 row; I *think* the PGSQL syntax is: Select from order by age asc limit 1; I have no idea if the optimizer does 'fast first' optimizations, so I don't know how quick this would be on a large table. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: +61-03-5367 7422 | _________ \ Fax: +61-03-5367 7430 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/