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

Ordering of records in group by not possible

From: Chris Kratz <chris(dot)kratz(at)vistashare(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Ordering of records in group by not possible
Date: 2006-04-26 13:19:41
Message-ID: 200604260919.41197.chris.kratz@vistashare.com (view raw or flat)
Thread:
Lists: pgsql-general
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.

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

select a,aggregate(b)
from (
   select a,b
   from c
   order by a,b
) as foo
group by a
order by a

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?

This is pg 8.1 on Linux.

Thanks,

-chris
-- 
Chris Kratz

Responses

pgsql-general by date

Next:From: Philippe LangDate: 2006-04-26 13:27:50
Subject: PL/Ruby, INOUT parameters / SETOF record
Previous:From: Simon RiggsDate: 2006-04-26 11:16:56
Subject: Re: Having problems with a 25 million row table on 8.1.3

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