Skip site navigation (1) Skip section navigation (2)

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

From: Julian Scarfe <jscarfe(at)callnetuk(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] DISTINCT ON: speak now or forever hold your peace
Date: 2000-01-25 08:30:36
Message-ID: 388D5F2C.DBD639D@callnetuk.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
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

In response to

Responses

pgsql-hackers by date

Next:From: Alfred PerlsteinDate: 2000-01-25 08:42:00
Subject: Re: [HACKERS] Oh btw, about XXX
Previous:From: Tom LaneDate: 2000-01-25 08:20:20
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace

pgsql-sql by date

Next:From: Peter EisentrautDate: 2000-01-25 10:30:30
Subject: Re: [SQL] Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
Previous:From: Tom LaneDate: 2000-01-25 08:20:20
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group