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

Reply via email to