Received: from mailo.vtcif.telstra.com.au (mailo.vtcif.telstra.com.au [202.12.144.17]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA66684; Mon, 24 Jan 2000 22:12:35 -0500 (EST) (envelope-from chris@bitmead.com) Received: (from uucp@localhost) by mailo.vtcif.telstra.com.au (8.8.2/8.6.9) id OAA22522; Tue, 25 Jan 2000 14:12:03 +1100 (EST) Received: from maili.vtcif.telstra.com.au(202.12.142.17) via SMTP by mailo.vtcif.telstra.com.au, id smtpdH0VwL_; Tue Jan 25 14:11:40 2000 Received: (from uucp@localhost) by maili.vtcif.telstra.com.au (8.8.2/8.6.9) id OAA06449; Tue, 25 Jan 2000 14:11:40 +1100 (EST) Received: from localhost(127.0.0.1), claiming to be "mail.cdn.telstra.com.au" via SMTP by localhost, id smtpdfz23r_; Tue Jan 25 14:11:09 2000 Received: from lunitari.nimrod.itg.telecom.com.au (lunitari.nimrod.itg.telecom.com.au [192.53.254.48]) by mail.cdn.telstra.com.au (8.8.2/8.6.9) with ESMTP id OAA05154; Tue, 25 Jan 2000 14:11:08 +1100 (EST) Received: from majere.design (majere [192.53.254.45]) by lunitari.nimrod.itg.telecom.com.au (8.9.1/8.9.3) with ESMTP id OAA21314; Tue, 25 Jan 2000 14:10:32 +1100 (EST) Received: from bitmead.com (localhost [127.0.0.1]) by majere.design (8.8.8+Sun/8.8.8) with ESMTP id OAA10809; Tue, 25 Jan 2000 14:09:50 +1100 (EST) Sender: chrisb@nimrod.itg.telstra.com.au Message-ID: <388D13FE.4E75CA05@bitmead.com> Date: Tue, 25 Jan 2000 14:09:50 +1100 From: Chris Bitmead Organization: IBM Global Services X-Mailer: Mozilla 4.6 [en] (X11; I; SunOS 5.6 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-hackers@postgreSQL.org CC: pgsql-sql@postgreSQL.org Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace References: <2295.948765015@sss.pgh.pa.us> <3.0.5.32.20000125140349.035768d0@mail.rhyme.com.au> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Philip Warner wrote: > >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 think what I really meant was... SELECT DISTINCT ON firstname firstname, age ORDER BY age. Which would find the youngest person called "fred", the youngest person called "paul", the youngest person called "jim" etc etc. which your limit example wouldn't do.