-----Original Message-----
From: Michael Glaesemann [mailto:[EMAIL PROTECTED]
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_type
from observation
group by vector_id, obs_type
) latest_observations
join observation using (date, vector_id, obs_type)
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
