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

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 <
> 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:
> Hoping it will help!
> Jean-Luc Hainaut
> On 3 Oct 2017, at 11:13pm, Doug Nebeker <> 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.
>> <>
>> 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 mailing list
sqlite-users mailing list

Reply via email to