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

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 |/