Hi,
I have a table:
observation ( id int4 NOT NULL [PRIMARY KEY], vector_id NOT NULL [FORGIEN KEY], obs_type VARCHAR(4) NOT NULL, date
TIMESTAMPNULL
)
I need to select the newest observation id, classify by type, for each
vector (there can be multiple observation ids on each vector). I have read
the postgresql manual and see that there is a "DISTINCT ON" statement which
can do this.
i.e.
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?
Thanks for you help
Jeremy