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
