Thread: Aggregate functions on ordered data?

Is it possible in _any_ way to control the order of the rows passed to an
aggregate function?

My problem is the following: I have tables with timestamps (PK) and bigint
values (gathered from snmp Counter32 entries) and I want to write an
aggregate function which sums over the differences between two entries which
follow one another in time and also accounts for range-wraps of the 32bit
values.

I have come up with a plpgsql version which does exactly that but as far as I
understand I can not be sure that the rows reach the aggregate sfunc in the
order of the timestamps. Is this order indetermined or is it always the order
of the rows in the table?

Anyway my problem remains: how to make sure the aggregate sfunc gets called in
the order I want?

Any help would be greatly appreciated!

Andreas Lehrbaum <andreas.lehrbaum@kabelweb.at> writes:
> Is it possible in _any_ way to control the order of the rows passed to an
> aggregate function?

For simple aggregation you can do

SELECT myagg(col) FROM (SELECT col FROM ... ORDER BY foo) ss;

If you wanted to group, you could try

SELECT gcol, myagg(col) FROM
  (SELECT gcol, col FROM ... ORDER BY gcol, foo) ss
GROUP BY gcol;

but this does *not* work reliably in current releases (it will work in
7.4 though).  See the archives.

            regards, tom lane