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
