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@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
