On Thu, Sep 25, 2014 at 12:54 PM, Kraijenbrink - FixHet - Systeembeheer < kraijenbr...@fixhet.nl> wrote: > > SELECT GROUP_CONCAT(Parent.fkintFolderID,'\') FilePath > FROM tblFolderNestedSets Node > , tblFolderNestedSets Parent > WHERE Node.intLeft BETWEEN Parent.intLeft AND Parent.intRight > AND Parent.fkintSessionID = Node.fkintSessionID > AND Node.fkintSessionID = 1817 > AND Node.fkintFolderID = 1937926; > > Query result: > > 1927916\1934826\1936323\1937926 >
from http://www.sqlite.org/lang_aggfunc.html: The order of the concatenated elements is arbitrary. So it's likely just chance that you get the result you expect, no? Aren't recursive CTEs supposed to be used for such hierarchical queries? Regarding group_concat performance, you could always try to write your own aggregate function and compare. Could be group_concat does not pre-allocate enough capacity into its buffer, and must realloc too many times. I doubt it accounts for a 10x slow down though, assuming my hypothesis even holds of course. SQLite is one of those SQL engines where you can use aggregate functions w/o an explicit group-by clause, so maybe w/o group_concat, that's no longer a group-by by just a join, ending up being faster. All speculations though, especially since I don't understand how your query works :). --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
