From: | "Jeremy Palmer" <palmerj(at)xtra(dot)co(dot)nz> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: DISTINCT ON |
Date: | 2005-11-19 02:53:14 |
Message-ID: | 20051119025318.KVHI14226.mta3-rme.xtra.co.nz@creeping |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)xtra(dot)co(dot)nz]
Sent: Saturday, 19 November 2005 11:05 a.m.
To: 'pgsql-general(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2005-11-19 02:54:54 | Re: [ANNOUNCE] Sun supporting PostgreSQL |
Previous Message | Guy Rouillier | 2005-11-19 02:37:23 | Re: Why CALL/PERFORM not part of core SQL? |