Re: DISTINCT ON
От | Jeremy Palmer |
---|---|
Тема | Re: DISTINCT ON |
Дата | |
Msg-id | 20051119025318.KVHI14226.mta3-rme.xtra.co.nz@creeping обсуждение исходный текст |
Ответ на | DISTINCT ON ("Jeremy Palmer" <palmerj@xtra.co.nz>) |
Список | pgsql-general |
Sorry I posted this to the wrong list. I have now reposted this is pgsql-sql. -----Original Message----- From: Jeremy Palmer [mailto:palmerj@xtra.co.nz] Sent: Saturday, 19 November 2005 11:05 a.m. To: 'pgsql-general@postgresql.org' 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
В списке pgsql-general по дате отправления: