Re: [BUGS] We are not following the spec for HAVING without GROUP

Bruno Wolff III <bruno(at)wolff(dot)to> writes:

> The case I was thinking of were datatypes without a defined ordering
> where max and min wouldn't be usable. But if GROUP BY was going to
> changed to allow any columns if the primary key was used in the GROUP
> BY clause, I can't see any use for those functions.

Well any other case will basically be a another spelling for DISTINCT ON.

Except DISTINCT ON only handles a limited range of cases. Basically DISTINCT
ON is a special case of GROUP BY where the _only_ aggregate function you're
allowed is first().

Handling the same cases using GROUP BY would let you mix other aggregate
functions so where you have:

select distinct on (x) x,y,z
order by x,y,z

You can do the equivalent:

select x, first(y), first(z)
order by x,y,z
group by x

But you can also handle the more general case like:

select x, first(y), first(z), avg(a), sum(s)
order by x,y,z
group by x

I don't really care one way or the other about the "first" function per se.

But it seems odd to have a feature to handle a special case of an existing
much more general feature separately. It seems it would be more worthwhile to
handle the general case of aggregate functions that don't need all the records
to generate an answer, including first(), last(), min(), and max(). That would
better handle the cases DISTINCT ON handles but also solve many other
problems.

--
greg