On 8 January 2018 at 09:19, petern <peter.nichvolo...@gmail.com> wrote:
> Your inner CTE will have to examine every generated row and count only
> matches toward "running_num".
Good idea, that works -
sqlite> with r(num, rand, running_num) as (select 1,
cast(round(abs(random())/9223372036854775808) as int), 1 union all select
num+1, cast(round(abs(random())/9223372036854775808) as int), case rand
when 1 then running_num+1 else running_num end from r) select running_num,
num from r where rand=1 limit 3;
P.S one reason I am using SQL instead the main application to perform such
calculations is precisely to avoid using variables (and hence the evils of
mutable state). Why do you say it is more efficient?
sqlite-users mailing list