I was trying to use
group_concat() to glue together some column values into a stiched together Markdown document. My first attempt lookd like this:
select group_concat('## ' || chapter || ' > ' || quote, ' ') from highlights order by timestamp
This attempt didn't work, because the order of the elements combined by a
group_concat() is undefined:
The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.
It turns out you can fix this using a subselect:
select group_concat('## ' || chapter || ' > ' || quote, ' ') from (select chapter, quote from highlights order by timestamp)
See this explanation by Keith Medcalf on the SQLite forum.
I think it may also be possible to solve this using Window functions. I tried doing this:
select group_concat('## ' || chapter || ' > ' || quote, ' ') OVER (ORDER BY timestamp) from highlights
Which almost worked... but it returned one row for each row in
highlights, each one with a growing combined result - the result I wanted was in the last returned row.
Created 2022-02-06T15:08:39-08:00 · Edit