Hi, I want to the count the number of leaves (descendants without children) for each node in a DAG
DAG definition - CREATE TABLE nodes(id integer primary key, description text); CREATE TABLE edges(parent not null references nodes, child not null references nodes, primary key(parent, child)); My query - CREATE VIEW v_count_leaves as with recursive r(id, top) as ( select id, id from nodes union all select t.id, top from nodes as t, edges as e, r where e.parent=r.id and t.id =e.child) select top, count(*) from r where top<>id and id not in (select parent from edges where parent=id) group by top; It seems to work but is complex to understand and debug despite my aim to keep it simple as possible, but more importantly - it is very slow when there are more than a few thousand nodes and edges. It there a more efficient (and ideally simpler) way? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
