Thanks for that. I find that an amazing solution and one that I would never have come up with. Indeed a lot simpler (for me, that is) in application code, but for my purpose this SQL is fine as my numbers are small, thousands at most.
RBS On Tue, Nov 17, 2015 at 12:09 AM, Igor Tandetnik <igor at tandetnik.org> wrote: > On 11/16/2015 6:37 PM, Bart Smissaert wrote: > >> Now, what I need is for every row the count of preceding rows where >> the ID is the same as the ID of the current row, the date is less than the >> date of the current row and the value is >= 10 and <= 20. If the value is >> outside >> this range then it should stop the count for that current row. If the >> value >> of the current row is outside this 10 to 20 range than the result is 0 and >> there >> is no need to count preceding rows. >> > > Personally, I'd do it in application code. It can be done in single pass. > If you insist on pure SQL solution, then something like this: > > select ID, DATE, VALUE, > (select count(*) from TEST prev > where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE > > coalesce((select max(DATE) from TEST boundary > where boundary.ID = T.ID and boundary.DATE <= T.DATE > and not(boundary.VALUE between 10 and 20)), '') > ) as COUNT_PRECEDING_IN_RANGE > from TEST T; > > Performance will likely be, shall we say, less than stellar. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
