So simple in hindsight, just add a second sort column 'close' to the union and then traverse the tree - thanks.
On 26 November 2017 at 15:44, Clemens Ladisch <clem...@ladisch.de> wrote: > Shane Dev wrote: > > Any ideas to achieve this? > > Use another CTE to bring all rows into the correct order. > Then a standard tree walk: > > WITH RECURSIVE > data AS ( > SELECT id, line, par, 0 AS close > FROM vtag > UNION ALL > SELECT id, line, par, 1 > FROM vparent_closetag > ORDER BY id, close -- this relies on the ids being equal > ), > t AS ( > SELECT id, line, 0 AS lev, close > FROM data > WHERE par IS NULL > > UNION ALL > > SELECT data.id, data.line, t.lev + 1, data.close > FROM t > JOIN data ON t.id = data.par > AND t.close = 0 > ORDER BY lev DESC > ) > SELECT lev, line FROM t; > > > Regards, > Clemens > _______________________________________________ > 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
