> -----Original Message-----
> From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
