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

Reply via email to