Re: DISTINCT ON

> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@myrealbox.com]
> Sent: Saturday, 19 November 2005 4:07 p.m.
> To: Jeremy Palmer
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] DISTINCT ON
> 
> [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_type
>     from observation
>     group by vector_id, obs_type
>     ) latest_observations
> join observation using (date, vector_id, obs_type)
> 
> Michael Glaesemann
> grzm myrealbox com

Thanks again for the help. This query looks close to what I need. I think
the only change is to use an explicit join criteria, as the max date alias
can't be used in the "using" clause - only table columns can.

Interesting enough, on my server the "distinct on" clause that I originally
ran takes 10% of execution time that the query you provided does.

Thanks for your help.

Jeremy Palmer