Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA11180; Tue, 25 Jan 2000 05:59:14 -0500 (EST) (envelope-from e99re41@DoCS.UU.SE) Received: from Val.DoCS.UU.SE (e99re41@Val.DoCS.UU.SE [130.238.9.94]) by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id LAA25822; Tue, 25 Jan 2000 11:59:06 +0100 (MET) Received: from localhost (e99re41@localhost) by Val.DoCS.UU.SE (8.6.12/8.6.12) with SMTP id LAA11995; Tue, 25 Jan 2000 11:59:04 +0100 X-Authentication-Warning: Val.DoCS.UU.SE: e99re41 owned process doing -bs Date: Tue, 25 Jan 2000 11:59:04 +0100 (MET) From: Peter Eisentraut Reply-To: Peter Eisentraut To: Julian Scarfe cc: Tom Lane , pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] DISTINCT ON: speak now or forever hold your peace In-Reply-To: <388D5F2C.DBD639D@callnetuk.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Tue, 25 Jan 2000, Julian Scarfe 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 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? select location, max(report_time) from reports group by location This also has the negligible advantage that it gives you determinate results. -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden