DISTINCT ON

From: "Jeremy Palmer" <palmerj(at)xtra(dot)co(dot)nz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: DISTINCT ON
Date: 2005-11-18 22:05:16
Message-ID: 20051118220518.BSRV1416.mta4-rme.xtra.co.nz@creeping
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2005-11-18 23:26:42 Re: Upgrading from 8.0 to 8.1 on Debian
Previous Message Mark Harrison 2005-11-18 21:38:59 implicit index created, do I need to also create an explicit index?