[sqlite] Re: [Bulk] Re: [sqlite] [OT] SQL: limit a query by sum(val)?

[Marcelo Zamateo]

Hi
 I'm very interested in "runing-functions"!
If the order is a problem, it seems to me that can be solved adding one  
more level of subqueries:

SELECT val, period
FROM (
   SELECT val, period, running_total(val) AS runner
   FROM (
        SELECT val, period
        FROM t1
      ORDER BY period DESC
     ) AS l3
) AS l2
WHERE runner < 40
ORDER BY period DESC
As far as i know sqlite has not native runing-sum.
Using custom functions i've got a kind of "one shot runing-functions".
With "one shot" i mean such functions can't be used in more than one  
column at a time.
The one shot limitation is because sqlite3_aggregate_context doesn't work  
from xFunc (strictly it isn't limitation, but would facilitate things...)
Is there a problem in making sqlite3_aggregate_context to work from xFunc?
Thank you.
Marcelo

At 6:50 PM +0100 2/11/05, Philipp Knüsel wrote:
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