From: Philip Warner <pjw@rhyme.com.au>
To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
Date: Thu, 27 Jan 2000 09:23:24 +1100
Message-ID: <3.0.5.32.20000127092324.03474eb0@mail.rhyme.com.au> (raw)
In-Reply-To: <Pine.LNX.4.21.0001260143120.9535-100000@localhost.localdom ain>
References: <2295.948765015@sss.pgh.pa.us>
At 19:34 26/01/00 +0100, Peter Eisentraut wrote:
>On 2000-01-24, Tom Lane mentioned:
>
>> If I don't hear loud hollers very soon, I'm going to eliminate the
>> DISTINCT ON "feature" for 7.0. As previously discussed, this feature
>> is not standard SQL and has no clear semantic interpretation.
>
>Our documents say that DISTINCT ON is equivalent to GROUP BY. I still
>don't see why that wouldn't be true. You can always rewrite
>
>select distinct on a a,b from test
>
>as
>
>select a, xxx(b) from test group by a
>
>where xxx is some aggregate function (presumably min or max).
>
>You can also rewrite
>
>select distinct on a a,b,c from test
>
>as
>
>select a, b, c from test group by a, b, c
>
>or using some aggregates here as well. At least you can control your
>results that way.
I only learned about DISTINCT ON in this discussion, but my impression is
that it has one advantage over GROUP BY, i that it produces a 'consistent'
tuple. ie. the tuple it produces is guaranteed to exist in the database,
whereas using GROUP BY with aggregates will not produce a 'real' row:
f1|f2|f3
--------
1 2 3
1 3 2
2 3 1
2 1 3
3 1 2
3 2 1
'select distinct on f1 f1,f2,f3 from <somewhere> order by f1, f2, f3' will
produce (I think):
f1|f2|f3
--------
1 2 3
2 1 3
3 1 2
...where each resulting tuple actually exists in the DB. I can't see a way
of doing this with aggreagates unless a 'FIRST' function is defined, but
that (as already discussed) will not obey the 'order by' clause. Maybe an
'ANY' aggregate would do it, but then it would not always produce the same
results.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: pjw@rhyme.com.au, peter_e@gmx.net, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
In-Reply-To: <3.0.5.32.20000127092324.03474eb0@mail.rhyme.com.au>
References: <2295.948765015@sss.pgh.pa.us> <3.0.5.32.20000127092324.03474eb0@mail.rhyme.com.au>
* 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