pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: DISTINCT ON
9+ messages / 6 participants
[nested] [flat]
* DISTINCT ON
@ 2002-09-13 02:49 Rudi Starcevic <rudi@oasis.net.au>
2002-09-13 02:48 ` Re: DISTINCT ON Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 9+ messages in thread
From: Rudi Starcevic @ 2002-09-13 02:49 UTC (permalink / raw)
To: pgsql-sql
Hi,
Just quickly, DISTINCT ON - Is that Postgresql only ?
Thanks
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2002-09-13 02:49 DISTINCT ON Rudi Starcevic <rudi@oasis.net.au>
@ 2002-09-13 02:48 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-09-13 14:35 ` Re: DISTINCT ON Chad Thompson <chad@weblinkservices.com>
0 siblings, 1 reply; 9+ messages in thread
From: Christopher Kings-Lynne @ 2002-09-13 02:48 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
Don't know. I've seen it elsewhere so it might even be "standard".
Chris
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rudi Starcevic
> Sent: Friday, 13 September 2002 10:50 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] DISTINCT ON
>
>
> Hi,
>
> Just quickly, DISTINCT ON - Is that Postgresql only ?
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2002-09-13 02:49 DISTINCT ON Rudi Starcevic <rudi@oasis.net.au>
2002-09-13 02:48 ` Re: DISTINCT ON Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-09-13 14:35 ` Chad Thompson <chad@weblinkservices.com>
0 siblings, 0 replies; 9+ messages in thread
From: Chad Thompson @ 2002-09-13 14:35 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
According to a recent thread Re: [SQL] Select the max on a field
Jeff Eckerman said:
>If you don't mind a non-portable feature, DISTINCT ON
>should do what you want. Something like:
>SELECT DISTINCT ON (att_2) att_1, att_2, att_3, att_4
>FROM table
>ORDER BY att_2, att_1 DESC;
I couldnt find anything in the docs, but it acording to this it isnt standard
Thanks
Chad
----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Rudi Starcevic" <rudi@oasis.net.au>; <pgsql-sql@postgresql.org>
Sent: Thursday, September 12, 2002 8:48 PM
Subject: Re: [SQL] DISTINCT ON
> Don't know. I've seen it elsewhere so it might even be "standard".
>
> Chris
>
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org
> > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rudi Starcevic
> > Sent: Friday, 13 September 2002 10:50 AM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] DISTINCT ON
> >
> >
> > Hi,
> >
> > Just quickly, DISTINCT ON - Is that Postgresql only ?
> > Thanks
> >
^ permalink raw reply [nested|flat] 9+ messages in thread
* DISTINCT ON
@ 2005-11-18 22:49 Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-18 23:27 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jeremy Palmer @ 2005-11-18 22:49 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2005-11-18 22:49 DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
@ 2005-11-18 23:27 ` Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 02:50 ` Re: DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2005-11-18 23:27 UTC (permalink / raw)
To: Jeremy Palmer <palmerj@xtra.co.nz>; +Cc: pgsql-sql
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...
>
> How would this be done in this scenario?
Something like:
select max(date), id
from observation
group by vector_id, obs_type;
Do test to see which is better in your situation.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2005-11-18 22:49 DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-18 23:27 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
@ 2005-11-19 02:50 ` Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-19 03:06 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jeremy Palmer @ 2005-11-19 02:50 UTC (permalink / raw)
To: pgsql-sql; +Cc: 'Michael Glaesemann' <grzm@myrealbox.com>
Thanks for the reply.
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.
Any further suggestions?
-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: Saturday, 19 November 2005 12:28 p.m.
To: Jeremy Palmer
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] DISTINCT ON
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...
>
> How would this be done in this scenario?
Something like:
select max(date), id
from observation
group by vector_id, obs_type;
Do test to see which is better in your situation.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2005-11-18 22:49 DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-18 23:27 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 02:50 ` Re: DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
@ 2005-11-19 03:06 ` Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 04:06 ` Re: DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
0 siblings, 1 reply; 9+ messages in thread
From: Michael Glaesemann @ 2005-11-19 03:06 UTC (permalink / raw)
To: Jeremy Palmer <palmerj@xtra.co.nz>; +Cc: pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2005-11-18 22:49 DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-18 23:27 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 02:50 ` Re: DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-19 03:06 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
@ 2005-11-19 04:06 ` Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-19 14:31 ` Re: DISTINCT ON Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 9+ messages in thread
From: Jeremy Palmer @ 2005-11-19 04:06 UTC (permalink / raw)
To: pgsql-sql; +Cc: 'Michael Glaesemann' <grzm@myrealbox.com>
> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@myrealbox.com]
> Sent: Saturday, 19 November 2005 4:07 p.m.
> To: Jeremy Palmer
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] DISTINCT ON
>
> [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
Thanks again for the help. This query looks close to what I need. I think
the only change is to use an explicit join criteria, as the max date alias
can't be used in the "using" clause - only table columns can.
Interesting enough, on my server the "distinct on" clause that I originally
ran takes 10% of execution time that the query you provided does.
Thanks for your help.
Jeremy Palmer
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: DISTINCT ON
2005-11-18 22:49 DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-18 23:27 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 02:50 ` Re: DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-19 03:06 ` Re: DISTINCT ON Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 04:06 ` Re: DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
@ 2005-11-19 14:31 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2005-11-19 14:31 UTC (permalink / raw)
To: Jeremy Palmer <palmerj@xtra.co.nz>; +Cc: pgsql-sql@postgresql.org, 'Michael Glaesemann' <grzm@myrealbox.com>
On Sat, Nov 19, 2005 at 17:06:27 +1300,
Jeremy Palmer <palmerj@xtra.co.nz> wrote:
>
> Interesting enough, on my server the "distinct on" clause that I originally
> ran takes 10% of execution time that the query you provided does.
If DISTINCT ON wasn't useful, I doubt it would have been added.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-11-19 14:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-13 02:49 DISTINCT ON Rudi Starcevic <rudi@oasis.net.au>
2002-09-13 02:48 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-09-13 14:35 ` Chad Thompson <chad@weblinkservices.com>
2005-11-18 22:49 DISTINCT ON Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-18 23:27 ` Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 02:50 ` Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-19 03:06 ` Michael Glaesemann <grzm@myrealbox.com>
2005-11-19 04:06 ` Jeremy Palmer <palmerj@xtra.co.nz>
2005-11-19 14:31 ` Bruno Wolff III <bruno@wolff.to>
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