Re: [SQL] DISTINCT ON: speak now or forever hold your peace

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