Looking at this approach of a hierarchical system: https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
Given a table like this: ID PARENT_ID FOLDER RANK --------------------------------------------------------------- 1 0 Main 000000001 2 1 CC 000000001-0000000002 3 1 BB 000000001-0000000003 4 1 AA 000000001-0000000004 5 2 B 000000001-0000000002-0000000005 6 2 A 000000001-0000000002-0000000006 What SQL should I use to update the field RANK if the first row is known to be 0000000001, but all the next rows are null? I tried with a non-recursive query, but couldn't work it out. RBS On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert <bart.smissa...@gmail.com> wrote: > This looks a nice and simple way to display the tree in the right order > without recursive SQL: > > https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql > > Will do some testing on large numbers to see how the 2 methods compare > speed-wise. > > RBS > > On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf <kmedc...@dessus.com> wrote: > >> >> See https://sqlite.org/lang_with.html >> >> which includes how to traverse the recursive tree in either depth-first >> or breadth-first order. >> >> Why do you need the closure table at all? >> >> >> create table folders >> ( >> id integer primary key, >> parent_id integer references folders, >> name text not null collate nocase, >> check (not (parent_id is null and id != 1)) >> ); >> >> insert into folders values (1, null, 'Folder1'), >> (2, 1, 'Folder2'), >> (3, 1, 'Folder3'), >> (4, 1, 'Folder4'), >> (5, 2, 'Folder5'), >> (6, 2, 'Folder6'); >> .head on >> .mode column >> .width 30 9 38 >> >> -- depth first >> >> with foo (id, parent_id, name, level, path) >> as (select folders.*, 0, folders.name >> from folders >> where parent_id is null >> union all >> select folders.*, level + 1, foo.path || '\' || folders.name >> from foo, folders >> where folders.parent_id = foo.id >> order by 4 >> ) >> select substr(' ', 1, (level - 1) * 4) || name as >> Folder, >> coalesce(parent_id, 0) as PARENT_ID, >> path as FullPath >> from foo; >> >> >> -- breadth first >> >> with foo (id, parent_id, name, level, path) >> as (select folders.*, 0, folders.name >> from folders >> where parent_id is null >> union all >> select folders.*, level + 1, foo.path || '\' || folders.name >> from foo, folders >> where folders.parent_id = foo.id >> order by 4 desc >> ) >> select substr(' ', 1, (level - 1) * 4) || name as >> Folder, >> coalesce(parent_id, 0) as PARENT_ID, >> path as FullPath >> from foo; >> >> >> >> SQLite version 3.27.0 2019-01-28 00:42:06 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> create table folders >> ...> ( >> ...> id integer primary key, >> ...> parent_id integer references folders, >> ...> name text not null collate nocase, >> ...> check (not (parent_id is null and id != 1)) >> ...> ); >> sqlite> >> sqlite> insert into folders values (1, null, 'Folder1'), >> ...> (2, 1, 'Folder2'), >> ...> (3, 1, 'Folder3'), >> ...> (4, 1, 'Folder4'), >> ...> (5, 2, 'Folder5'), >> ...> (6, 2, 'Folder6'); >> sqlite> .head on >> sqlite> .mode column >> sqlite> .width 30 9 38 >> sqlite> >> sqlite> -- depth first >> sqlite> >> sqlite> with foo (id, parent_id, name, level, path) >> ...> as (select folders.*, 0, folders.name >> ...> from folders >> ...> where parent_id is null >> ...> union all >> ...> select folders.*, level + 1, foo.path || '\' || >> folders.name >> ...> from foo, folders >> ...> where folders.parent_id = foo.id >> ...> order by 4 >> ...> ) >> ...> select substr(' ', 1, (level - 1) * 4) || name >> as Folder, >> ...> coalesce(parent_id, 0) as PARENT_ID, >> ...> path as FullPath >> ...> from foo; >> Folder PARENT_ID FullPath >> ------------------------------ --------- >> -------------------------------------- >> Folder1 0 Folder1 >> Folder2 1 Folder1\Folder2 >> Folder3 1 Folder1\Folder3 >> Folder4 1 Folder1\Folder4 >> Folder5 2 Folder1\Folder2\Folder5 >> Folder6 2 Folder1\Folder2\Folder6 >> sqlite> >> sqlite> >> sqlite> -- breadth first >> sqlite> >> sqlite> with foo (id, parent_id, name, level, path) >> ...> as (select folders.*, 0, folders.name >> ...> from folders >> ...> where parent_id is null >> ...> union all >> ...> select folders.*, level + 1, foo.path || '\' || >> folders.name >> ...> from foo, folders >> ...> where folders.parent_id = foo.id >> ...> order by 4 desc >> ...> ) >> ...> select substr(' ', 1, (level - 1) * 4) || name >> as Folder, >> ...> coalesce(parent_id, 0) as PARENT_ID, >> ...> path as FullPath >> ...> from foo; >> Folder PARENT_ID FullPath >> ------------------------------ --------- >> -------------------------------------- >> Folder1 0 Folder1 >> Folder2 1 Folder1\Folder2 >> Folder5 2 Folder1\Folder2\Folder5 >> Folder6 2 Folder1\Folder2\Folder6 >> Folder3 1 Folder1\Folder3 >> Folder4 1 Folder1\Folder4 >> sqlite> >> >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to Heaven >> says a lot about anticipated traffic volume. >> >> >> >> >> >> _______________________________________________ >> 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
