Re: Ordering of records in group by not possible

On Wed, Apr 26, 2006 at 09:19:41AM -0400, 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
>
> Is not accepted by postgres. This will only work if you order by a. But,
> this means that the records that are grouped are processed in no apparent
> order.

Well, ORDER BY happens *after* the select values have been calculated,
so it can't possibly affect the order of the rows into the aggregate.

> We have some custom aggregate functions where the order of the rows is
> important. Is there no way to do this without a subselect?

You found the right solution, use ORDER BY in a subselect.

> Even with a subselect doing the ordering ahead of time, is there any
> guarrantee that the records will be processed in the group by with the
> specified order? Or will the group by always be arbitrary in it's ordering
> of the records?

Currently, if the sub-select orders the rows, the outer query will see
the rows in that order. I don't think the SQL standard even allows
ORDER BY there, but PostgreSQL does support it for this reason. BTW,
GROUP BY doesn't not imply any ordering at all, consider a Hash
Aggregate that calculates all the aggregates simultaneously...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.