select * from t1 where period < '2003-1' order by period desc: -- val period sum(val) -- ----------- -- 15 2002-4 15 -- 10 2002-3 25 -- 5 2002-2 30 -- 15 2002-1 45 -- 10 2001-4 -- 5 2001-3
I need only the first records to fulfill sum(val) >= 40 (or all records if sum(val) < 40)
-- so the result should be limited to: -- val period -- ----------- -- 15 2002-4 -- 10 2002-3 -- 5 2002-2 -- 15 2002-1
It appears to me that you have a multi-part problem to solve here.
The first part of the problem is that you have to calculate a "running sum", and I don't know if there is any way to do this in a simple manner.
Given how SQL works, *if* there were a running_sum() function, it would need to execute after the ORDER BY clause, because a running sum only makes sense in the context of already sorted results.
Perhaps then the query might look something like this:
SELECT val, period FROM ( SELECT val, period, running_total(val) AS runner FROM t1 ORDER BY period DESC ) AS l2 WHERE runner < 40 ORDER BY period DESC
I don't see this being a simple problem yet, in any case. I don't know if the current mechanism for writing your own functions will let you execute them at the necessary time. (The example I gave may be wrong, if the SELECT line executes prior to ORDER BY.)
-- Darren Duncan
