Re: order by and aggregate

> Richard Huxton wrote:
>
>> On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote:
>>
>> >Hi
>> >I created my own aggregate function working as max(sum(value))
>> >It adds positive and negative values and finds maximum of this sum.
>> >To work properly this function needs data to be sorted.
>>
>>
>> I'm not sure that an aggregate function should require data to be sorted
>> before processing. Could you show details of your function - there may
>> be a
>> way to rewrite it to handle unsorted data.
>
> Standard Postgresql aggregate functions don't need sorted data, but my
> function needs. Look at the data:
> <value> <sum>
> 3 3
> -2 1
> 6 7 *** max_sum=7
> -3 4
> 2 6
>
> For example, if you inverse your data, you have:
> <value> <sum>
> 2 2
> -3 -1
> 6 5
> -2 3
> 3 6 *** max_sum=6
>
> As you see, data order is very important in this aggregate.

Ah - so it's maximum of a running-total rather than a sum.

AFAIK you are out of luck with aggregate functions. The order data is
supplied to them is *not* defined - the "order by" operates just before
results are output. Not much you can do about this, the whole basis of
relational DBs are sets and sets don't have any idea of ordering.

However, there are three options:

You could define a cursor (or a table-function in 7.3) which would handle
the order-by and then calculate the running-total on the fly. You then
just need a standard max(running_total) call to pick out the value.
Actually, if you use the cursor you might need to implement the max() in
the application.

Alternatively, you could add a running_total column and use a trigger to
ensure the value is kept up to date.

Finally, you could do the work in the application.

Difficult to say which is the best for you. If you have 7.3, don't need
these figures often and do a lot of updates/inserts I'd recommend option
1. If you're using 7.2, don't do a lot of inserts and want the figures
frequently I'd choose option 2.

HTH

- Richard Huxton