Hello SQLite-Users
This is offtopic, but as I would like to use it within SQLite... so let's try:
I would like to reduce the resultset of a query like using limit. But instead of using the number of records by the sum of values.
A small example: create table t1 (val int, period text); insert into t1 values ( 5, '2001-3'); insert into t1 values (10, '2001-4'); insert into t1 values (15, '2002-1'); insert into t1 values ( 5, '2002-2'); insert into t1 values (10, '2002-3'); insert into t1 values (15, '2002-4'); insert into t1 values (10, '2003-1'); insert into t1 values ( 5, '2003-2'); insert into t1 values (25, '2003-3'); insert into t1 values ( 5, '2003-4');
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
Sure, there is always the way to reduce the resultset within an application. Is there a way of using a subselect?
I asked google, the nearest term that comes to my mind is "sliding window", but maybe someone of you knows much better.
Your time is truly appreciated. Thank you!
Kind regards
Philipp