You could also write it even more clearly as: WITH IndexedLines AS (SELECT LineText FROM DocLines WHERE DocID = 10 ORDER BY LineIndex) SELECT group_concat(LineText, char(10)) FROM IndexedLines;
That code will actually work. As it is not C, SQLite will not recognize the '\n' C escaped line feed in Simon's example. Special characters, like char(10), must be generated by call out to an extension or values from a table. As was explained many times, despite malicious insistence on the preservation of obtuse comments in the group_concat() docs, the output of group_concat() IS directly controlled by the order of the supplied rowset and one is free to specify that order directly. Group_concat() itself is not idiosyncratically jumbling the output order. If the optimizer's discretionary reordering is to be communicated in the documents of built in functions then, for completeness, every aggregate function should have the same absurd disclaimer of inexorable randomness. Consider the aggregate round off error of the built in avg() function. By the same illogic isn't the roundoff error of the avg() output both intractable and implacable because the optimizer might change the order of the rows of an unordered query? Yet, it is completely true that one is free to specify a round off error minimizing order if one chooses to do so. OK. So everybody in favor of flawed documentation, raise their hand. The avg() function should also have the same obtuse remark suggesting, with a wink and nod, that that using avg() comes with an inexorable and implacable input ordering that one cannot control. On Wed, Oct 4, 2017 at 2:12 AM, Jean-Luc Hainaut <jean-luc.hain...@unamur.be > wrote: > On 04/10/2017 02:16, Simon Slavin wrote: > > The differences between SQLite and (a.o.) MySQL versions of "group_concat" > are a recurrent topic. > Since I often need to specify "distinct", "order by", "order direction" > and "separator", I have written a simple UDF class that simulates the MySQL > full version. > It is written in Python 2.7 through the standard SQLite3 interface but it > should be easy to translate it in C: > > https://www.dropbox.com/s/ilpx8duppbus8u3/group_concat2.py?dl=0 > > Hoping it will help! > > Jean-Luc Hainaut > > On 3 Oct 2017, at 11:13pm, Doug Nebeker <ad...@poweradmin.com> wrote: >> >> How can I select a document and get the complete sorted text back in a >>> single row (so I can do a JOIN on a different table with additional >>> information)? >>> >> There is a way which will probably work but the documentation adds a >> careful note that it will not always work. >> >> <https://sqlite.org/lang_aggfunc.html#groupconcat> >> >> So you would want something like >> >> SELECT group_concat(LineText, '\n') FROM >> (SELECT LineText FROM DocLines >> WHERE DocID = 10 >> ORDER BY LineIndex) >> >> The problem is that the order of concatenation is arbitrary, in other >> words you shouldn’t rely on this working. >> >> If you don’t want to use group_concat(), do it in your programming >> language. Use >> >> SELECT LineText FROM DocLines >> WHERE DocID = 10 >> ORDER BY LineIndex >> >> and concatenate the retrieved values in your programming language. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
