it seems the only way to do this is the old school method of joining back to the same table. was hoping for an equivalent to the OVER clause you get in SQL Server.
if anyone knows how to get a rownumber for each row I'd also like to know that.
thanks Robert, I got this working by joining to the same table with a >= (as opposed to subquery) but same idea.
Christopher,
Your cursor method was pretty close to how I've ended up solving my task.
The problem I was trying to solve involved comparing a comulativeTotal with a previouscomulativeTotal. I had a go at trying to do this in SQL this morning but due to the fact I'd be joining back to the same table once to get the comulative total and yet again to get back to the previouscomulative total it meant the query took ages. I went with a embedded cursor in object script and created a recordset at the end to pass back both comulativeTotal and previouscomulativeTotal at the same time. The iterative approach worked much better here as the two totals were close (one iteration away) ..