Re: [SQL] order by and aggregate

[Tomasz Myrta]

[EMAIL PROTECTED] wrote:

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

Sorry, my english still has a lot of black-holes :-(

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.

You are right, but if it is possible to use some tricks, why not to use them?

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.

All of these options look a bit difficult.
Currently I use pl/pgsql function and query with sorted subselect:
select maxsum(X.val) from (select val from some_table order by key) X
It isn't a very big problem for me. I was just wondering if I can change this pl/pgsql function in a view. Few weeks ago I asked on pgsql-performance about views and subselects. The conclusion was that postgresql planner doesn't work well when joining subselects - it wastes time on querying all rows of subselect.

1. I think it could rather slow down than speed up my solution.
2. I can't store this value, because each time data range changes.
3. I want to do as much as possible inside postgres.

I think I will have to stay with pl/pgsql function and sorted subquery.

Thanks for your help,
Tomasz Myrta


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html