Re: DISTINCT ON

Thanks for the reply.

Unfortunately that does not work as "id" column needs to be included in the
group by statement or be used in an aggregate function. If I did this it
definitely would note return the correct answer, as the "id" column is the
primary key for the table.

Any further suggestions?

-----Original Message-----
From: Michael Glaesemann [mailto:grzm(at)myrealbox(dot)com]
Sent: Saturday, 19 November 2005 12:28 p.m.
To: Jeremy Palmer
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] DISTINCT ON

On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote:

> SELECT DISTINCT ON (vector_id, obs_type)
> id
> FROM observation
> ORDER BY vector_id,
> obs_type,
> date DESC;
>
> However the documentation also states that "DISTINCT ON" is not
> part of the
> SQL standard and should be avoided when possible, stating that
> aggregations
> and sub-queries should be used instead...
>
> How would this be done in this scenario?

Something like:

select max(date), id
from observation
group by vector_id, obs_type;

Do test to see which is better in your situation.

Michael Glaesemann
grzm myrealbox com