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

Reply via email to