On 12/2/2014 1:47 PM, Paul Sanderson wrote:
WITH RECURSIVE rcte AS (SELECT rtable.ID,
rtable.parent,
rtable.FileName
FROM rtable
WHERE rtable.ID = 510
UNION ALL
SELECT rtable.ID,
rtable.parent,
rtable.FileName
FROM rcte
INNER JOIN rtable ON rcte.parent = rtable.ID
WHERE rtable.FileName <> '.'
LIMIT 20)
SELECT Group_Concat(rcte.FileName, '\') AS col1
FROM rcte
ORDER BY rcte.ID
This however appends the path in the wrong order, i.e. I get the file
name first and the root folder last
Try this:
select group_concat(FileName, '\') as col1 from (
WITH RECURSIVE rcte ...
SELECT rcte.FileName as FileName
FROM rcte
ORDER BY rcte.ID
);
That is, select individual rows, put them in the right order, then do
group_concat as an extra layer on top.
I don't believe it's technically guaranteed that rows will be grouped in
the right order, but it's very likely to work in practice.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
