Thread: 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

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