Re: Ordering of records in group by not possible

Chris Kratz wrote:
> On Wednesday 26 April 2006 10:30 am, you wrote:
> Hello Alban,
>
> The point is that the aggregates we are working on in our application are
> order sensitive. One common example of order sensitive aggregates are the
> first and last aggregate functions found in some other databases. Both of

Ah, order sensitive aggregates... Of course. I've been playing with the
thought of implementing first and last aggregates myself, but never got
around to it. It is not that often that you need those, the same result
can often be achieved by ordering and limitting.

Ordering the data before aggregating should do the trick though, but
you'll need to order your records in the subquery. Something like this,
I think:

Select a, aggregate(b)
from (
select a, b
from c
order by a,b
) d
group by a

Ordering on the aggregated values afterwards shouldn't make a difference
to the aggregated results. I'm pretty sure of that.

> The best solution we have found so far is to feed the aggregating query with a
> subquery that orders on the appropriate columns. But even then, since the
> group by forces another sort, I'm not convinced that the ordering is still
> not indeterminate.

The experts on the internals will know for sure, but I think their
answer'll be that the ordering is determinate this way.

> I'm almost convinced that there isn't a way to do order sensitive aggregates
> in pg, but will have to pull the record set out and process it
> programmatically externally if the subquery trick doesn't work.

No matter how you're going to achieve your aggregate, you'll need to
order the records you're aggregating over before doing so. Whether that
can be done in SQL, a stored procedure or in application code doesn't
change that.

I don't know where your idea to order on the original column outside the
aggregate originated, but it looks like a rather confusing way to
achieve this. It may even be indeterminate... It completely depends on
the database system knowing what the aggregate is about, AFAICS.

For the record, I'm just a regular here. I'm not directly involved with
PostgreSQL.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //