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: Fri, 28 Jan 2000 12:21:18 +1100
Message-ID: <3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au> (raw)
In-Reply-To: <Pine.LNX.4.21.0001271922100.356-100000@localhost.localdoma in>
References: <18389.948912054@sss.pgh.pa.us>
At 23:28 27/01/00 +0100, Peter Eisentraut wrote:
>
>select one.a, two.b, two.c
>from
> (select a, min(b) as "min_b" from test group by a) as one,
> (select b, c from test) as two
>where one."min_b" = two.b
>
>Not sure if this is completely legal as it stands but at least the idea
>would be to join the grouped select with the plain one to get the c
>corresponding to the minimum b. But of course we don't offer that, so it's
>distinct on until then. (It would really surprise me if the distinct on
>functionality was not at all possible to emulate using SQL, since in my
>experience it is fairly complete with regards to querying options at
>least.)
You are quite right - with a complete SQL impleentation, DISTINCT ON
becomes superfluous. Although it may give the optimizer usefull hints as to
how to approach the query. You actually have to be a bit more carefull to
avoid duplicates, something like:
Select
a,b,c
From
(Select a, min(b) from test group by a) as one(a,min_b),
(Select b, c from test t2 where t2.a = one.a limit to 1 row) as two
This is legal on the DB I use most of the time.
----------------------------------------------------------------
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.20000128122118.009d2100@mail.rhyme.com.au>
References: <18389.948912054@sss.pgh.pa.us> <3.0.5.32.20000128122118.009d2100@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