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

On 2000-01-26, Tom Lane mentioned:

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > 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).
>
> Not really. Look at Julian's example. He can't rewrite as
>
> select a, min(b), min(c) from test group by a
>
> because the idea is to get the c that corresponds to the min b.

I see. I believe what you want is this:

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.)

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden