Skip site navigation (1) Skip section navigation (2)

Re: Ordering of records in group by not possible

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 (view raw or flat)
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.

In response to

pgsql-general by date

Next:From: Alban HertroysDate: 2006-04-26 14:30:53
Subject: Re: Ordering of records in group by not possible
Previous:From: Reid ThompsonDate: 2006-04-26 13:35:24
Subject: Linux Journal article - Open source baseball stats web site will use PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group