Sorry I posted this to the wrong list. I have now reposted this is pgsql-sql. -----Original Message----- From: Jeremy Palmer [mailto:palmerj@xxxxxxxxxx] Sent: Saturday, 19 November 2005 11:05 a.m. To: 'pgsql-general@xxxxxxxxxxxxxx' Subject: DISTINCT ON 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 TIMESTAMP NULL ) 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
