Re: Ordering of records in group by not possible

On Wednesday 26 April 2006 10:30 am, you wrote:
> Chris Kratz wrote:
> > Hello all,
> >
> > I wanted to verify what we are seeing.
> >
> > Select a, aggregate(b)
> > from c
> > group by a
> > order by a,b
>
> That's a rather odd query... Values in b aren't available to order by,
> as they have been aggregated. There is no relation to the values in b
> and the values in your result set.
>
> You could order by "column 2" if you want to order on the results on
> your aggregate:
> Select a, aggregate(b)
> from c
> group by a
> order by a,2

Hello Alban,

The point is that the aggregates we are working on in our application are
order sensitive. One common example of order sensitive aggregates are the
first and last aggregate functions found in some other databases. Both of
which are very dependant on the order of the records going into the
aggregate. Since you can't order the records before they hit the group by in
postgres, the actual ordering of the records when they hit the aggregate
function is indeterminate and so order sensitive aggregates really don't
work.

The best solution we have found so far is to feed the aggregating query with a
subquery that orders on the appropriate columns. But even then, since the
group by forces another sort, I'm not convinced that the ordering is still
not indeterminate.

I know you can use distinct on to get similar behavior for first and last, but
that's not a general solution for aggregates that are order sensitive.

I'm almost convinced that there isn't a way to do order sensitive aggregates
in pg, but will have to pull the record set out and process it
programmatically externally if the subquery trick doesn't work.

-Chris

--
Chris Kratz