Hello,
Here is a view which assigns randomly chosen parents to a sequence of
children -
CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1
union all select abs(random())%child+1, child+1 from r) select * from r
limit 5;
sqlite> select * from v_random_hierarchy;
parent child
1
1 2
1 3
1 4
4 5
Can a column be added to this view which counts the number of children for
each parent?
Here is my failed attempt -
sqlite> select v1.parent, v1.child, (select count(*) from
v_random_hierarchy as v2 where v2.parent=v1.parent) as
number_children_of_parent from v_random_hierarchy as v1;
parent child number_children_of_parent
1 0
1 2 3
2 3 0
3 4 0
3 5 1
in this case, it should be -
parent child number_children_of_parent
1 1
1 2 1
2 3 1
3 4 2
3 5 2
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
