From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Ordering of records in group by not possible |
Date: | 2006-04-26 13:58:16 |
Message-ID: | 20060426135816.GC11828@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2006-04-26 14:30:53 | Re: Ordering of records in group by not possible |
Previous Message | Reid Thompson | 2006-04-26 13:35:24 | Linux Journal article - Open source baseball stats web site will use PostgreSQL |