Tom Lane <tgl@sss.pgh.pa.us> writes:
> Greg Stark <gsstark@mit.edu> writes:
> > It would also make it possible to deprecate DISTINCT ON in favour of GROUP BY
> > with first() calls.
>
> Oh? How is a first() aggregate going to know what sort order you want
> within the group? AFAICS first() is only useful when you honestly do
> not care which group member you get ... which is certainly not the case
> for applications of DISTINCT ON.
It would look something like
select x,first(a),first(b) from (select x,a,b from table order by x,y) group by x
which is equivalent to
select DISTINCT ON (x) x,a,b from table ORDER BY x,y
The group by can see that the subquery is already sorted by x and doesn't need
to be resorted. In fact I believe you added the smarts to detect that
condition in response to a user asking about precisely this type of scenario.
This is actually more general than DISTINCT ON since DISTINCT ON is basically
a degenerate case of the above where the _only_ aggregate allowed is first().
The more general case could have first() as well as other aggregates, though
obviously they would make it unlikely that any optimizations would be
applicable.
I do kind of like the DISTINCT ON syntax, but the inability to use any other
aggregate functions makes me often have to convert queries I originally wrote
to use it to use the more general GROUP BY and first() instead.
--
greg
