Received: from smtp.callnetuk.com (smtp.callnetuk.com [212.67.128.5]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA72598; Tue, 25 Jan 2000 03:30:14 -0500 (EST) (envelope-from jscarfe@callnetuk.com) Received: from callnetuk.com [212.67.129.162] by smtp.callnetuk.com with ESMTP (SMTPD32-5.05) id AF1322E400E4; Tue, 25 Jan 2000 08:30:11 +0000 Message-ID: <388D5F2C.DBD639D@callnetuk.com> Date: Tue, 25 Jan 2000 08:30:36 +0000 From: Julian Scarfe X-Mailer: Mozilla 4.04 [en] (WinNT; I) MIME-Version: 1.0 To: Tom Lane CC: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] DISTINCT ON: speak now or forever hold your peace References: <2295.948765015@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 use 'distinct on' to get the most recent reports for each of a group of locations. E.g.: create table reports ( location varchar(16), report_time datetime, report_text text); select distinct on location * from reports where location ~~ 'Lond%' order by location, reporttime desc; to get the tuples that offer the most recent reports for each of London, Londonderry, Londy etc. Is there an alternative? Julian Scarfe