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:[EMAIL PROTECTED] 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 ---------------------------(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
