Received: from c2bapps1.btconnect.com (c2bapps1.btconnect.com [193.113.209.21]) by hub.org (8.9.3/8.9.3) with SMTP id IAA46149 for ; Tue, 25 Jan 2000 08:31:28 -0500 (EST) (envelope-from jscarfe@callnetuk.com) Received: from smtp.innoviatech.com (actually host 195-171-219-176.btconnect.com) by c2bapps1 with SMTP (XT-PP); Tue, 25 Jan 2000 13:30:07 +0000 Received: from [10.0.0.6] by smtp.innoviatech.com (AppleShare IP Mail Server 6.3) id 40569 via TCP with SMTP; Tue, 25 Jan 2000 13:25:20 +0000 User-Agent: Microsoft Outlook Express Macintosh Edition - 5.0 (1513) Date: Tue, 25 Jan 2000 13:23:58 +0000 Subject: Re: [SQL] DISTINCT ON: speak now or forever hold your peace From: Julian Scarfe To: Peter Eisentraut CC: Tom Lane , pgsql-sql Message-ID: In-Reply-To: Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit on 25/1/00 10:59, Peter Eisentraut at e99re41@DoCS.UU.SE wrote: > On Tue, 25 Jan 2000, Julian Scarfe wrote: >> 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. But I want report_text *too* -- that's the important bit, and it's not an aggregate or common column in the group. So: > Am I wrong or can DISTINCT ON not be rewritten in terms of GROUP BY in all > cases? I don't think so. It has to be something like: select * from reports as r1 where r1.report_time = (select max(report_time) from reports as r2 where r2.location = r1.location) and r1.location ~~ 'Lond%'; However, to my surprise, that's as quick as doing the 'distinct on'. host=> explain select * from reports as r1 where r1.report_time = (select max(report_time) from reports as r2 where r2.location = r1.location) and r1.location ~~ 'Lond%'; NOTICE: QUERY PLAN: Index Scan using reports_by_location_issuetime on reports r1 (cost=186.24 size=2 width=334) SubPlan -> Aggregate (cost=3.62 size=0 width=0) -> Index Scan using reports_by_location_report_time on reports r2 (cost=3.62 size=13 width=8) EXPLAIN host=> explain select distinct on location * from reports where location ~~ 'Lond%' order by location, report_time desc; NOTICE: QUERY PLAN: Unique (cost=186.24 size=0 width=0) -> Sort (cost=186.24 size=0 width=0) -> Index Scan using reports_by_location_issuetime on reports (cost=186.24 size=2 width=334) I'm surprise that the "reports_by_location_issuetime" index got used, since it involves the issuetime field which is not the same as the report_time field. I edited it and numerous other fields from the quoted 'create table' for simplicity. There's also a "reports_by_location_report_time" which indexes on, obviously, (location, report_time). Comments? Julian Scarfe, learning...