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@myrealbox.com]
Sent: Saturday, 19 November 2005 12:28 p.m.
To: Jeremy Palmer
Cc: pgsql-sql@postgresql.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