Re: DISTINCT ON

> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@myrealbox.com]
> Sent: Saturday, 19 November 2005 12:28 p.m.

> 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...

> Something like:
>
> select max(date), id
> from observation
> group by vector_id, obs_type;

On Nov 19, 2005, at 11:50 , Jeremy Palmer wrote:

> 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.

[Please don't top post. It makes the post harder to read. I've  
reordered the post for readability.]

Try something like this:

select id
from (select max(date) as date    vector_id, obs_typefrom observationgroup by vector_id, obs_type) latest_observations
join observation using (date, vector_id, obs_type)

Michael Glaesemann
grzm myrealbox com