Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace

From: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
Date: 2000-01-28 01:30:28
Message-ID: 3.0.5.32.20000128123028.00cf6100@mail.rhyme.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Bd SQL (for the equality on min_b). Sorry for the two messages:

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 and t2.b = min_b 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 |/

Browse pgsql-hackers by date

  From Date Subject
Next Message Assaf Arkin 2000-01-28 01:45:23 TID clarification
Previous Message Philip Warner 2000-01-28 01:21:18 Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace