Your inner CTE will have to examine every generated row and count only
matches toward "running_num". You'll also need another column like
"last_running_num" with a referencing CASE statement in the "running_num"
column to condition emitting, for example, a non-null "running_num"...
Or you can simply and more efficiently use a local variable extension like
auxint.c on your existing VIEW (without the LIMIT) like so:
sqlite> .load auxint
sqlite> SELECT auxint('id',1)running_num,num FROM vrand limit 10;
running_num,num
1,1
2,2
3,3
4,4
5,5
6,9
7,11
8,14
9,15
10,16
The ~20 lines of code for auxint.c are here:
https://www.mail-archive.com/sqlite-users@mailinglists.sqlit
e.org/msg107018.html
On Sun, Jan 7, 2018 at 10:37 PM, Shane Dev <devshan...@gmail.com> wrote:
> Hello,
>
> The view VRAND below generates a series of 3 randomly chosen integers -
>
> CREATE VIEW vrand as with r(num, rand) as (
> select 1, cast(round(abs(random())/9223372036854775808) as int)
> union all
> select num+1, cast(round(abs(random())/9223372036854775808) as int) from
> r)
> select num from r where rand=1 limit 3;
>
> sqlite> select * from vrand;
> num
> 1
> 2
> 4
>
> sqlite> select count(*) from vrand;
> count(*)
> 3
>
> Now I would like to add a "running number" column with a result set like -
>
> running_num, num
> 1, 1
> 2, 2
> 3, 4
>
> However, the follow statement seems to trigger an infinite loop -
>
> select (select count(*) from vrand where num <= v.num), num from vrand as
> v;
>
> How can a running number column be added to this view?
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
