From: Michael Glaesemann <grzm@myrealbox.com>
To: Jeremy Palmer <palmerj@xtra.co.nz>
Cc: pgsql-sql@postgresql.org
Subject: Re: DISTINCT ON
Date: Sat, 19 Nov 2005 12:06:47 +0900
Message-ID: <DF11CA06-C2B3-46A2-B321-D827A1F2D4B5@myrealbox.com> (raw)
In-Reply-To: <20051119025058.LHSU22512.web3-rme.xtra.co.nz@creeping>
References: <20051119025058.LHSU22512.web3-rme.xtra.co.nz@creeping>
> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@myrealbox.com]
> Sent: Saturday, 19 November 2005 12:28 p.m.
> On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote:
>
>> 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...
> Something like:
>
> select max(date), id
> from observation
> group by vector_id, obs_type;
On Nov 19, 2005, at 11:50 , Jeremy Palmer wrote:
> Unfortunately that does not work as "id" column needs to be
> included in the
> group by statement or be used in an aggregate function. If I did
> this it
> definitely would note return the correct answer, as the "id" column
> is the
> primary key for the table.
[Please don't top post. It makes the post harder to read. I've
reordered the post for readability.]
Try something like this:
select id
from (
select max(date) as date
vector_id, obs_type
from observation
group by vector_id, obs_type
) latest_observations
join observation using (date, vector_id, obs_type)
Michael Glaesemann
grzm myrealbox com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@myrealbox.com, palmerj@xtra.co.nz
Subject: Re: DISTINCT ON
In-Reply-To: <DF11CA06-C2B3-46A2-B321-D827A1F2D4B5@myrealbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox