| From: | "Jeremy Palmer" <palmerj(at)xtra(dot)co(dot)nz> | 
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> | 
| Subject: | DISTINCT ON | 
| Date: | 2005-11-18 22:49:49 | 
| Message-ID: | 20051118224951.CFXK1416.mta4-rme.xtra.co.nz@creeping | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Glaesemann | 2005-11-18 23:27:57 | Re: DISTINCT ON | 
| Previous Message | Dean Gibson (DB Administrator) | 2005-11-18 21:16:21 | Re: Export to XML |