Hm thanks - actually thinking about it sorting by ID doesn't work as it is quite likely that the folder order doesn't follow the id/parent order, i.e. a parent folder could have an ID greater than one or more of its children.
So what I really need is to a) ensure that the ordering of the recursive query is guranteed, i.e. the order follows the ID/parent relationship to the root b) a method of concatenating this in reverse order This is an academic exercise so a solution is not actually required but interesting non the less Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 2 December 2014 at 19:36, Igor Tandetnik <i...@tandetnik.org> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
