In SQLite, will multiple colums order the same way with group_concat?

Question

I have a table like this:

title      id     name
manager.....1......bob
manager.....2......tim
manager.....3.....suzy
 worker.....4.....john

And I'm doing a query: select title, group_concat(id), group_concat(name) group by title

I get the result:

manager......1,2,3......bob,tim,suzy   <-- id order and name order match
 worker..........4..............john

My question is, are the id orders and name orders guaranteed to match? The SQLite manual says that: "The order of the concatenated elements is arbitrary." Does this mean that I run the risk of getting a result such as:

manager......3,1,2......bob,tim,suzy   <-- id order doesn't match name order
 worker..........4..............john

I've never seen this happen in my testing, but I need to know for sure before going to production.

Thanks!

Edit: Just to clarify, I don't care about the specific order at all. I only care that the order of group_concat(id) is the same order as group_concat(name). That's my question.

Possible duplicate of [Sqlite group\_concat ordering](https://stackoverflow.com/questions/1897352/sqlite-group-concat-ordering) — szmate1618, Jul 17 '19 at 13:18

score 0 · Answer 1 · answered Jun 23 '17 at 07:53

The word "arbitrary" means "arbitrary".

Anyway, group_concat() will accumulate the values in the order in which it happens to read the actual rows. So if you care about the returned values, you should sort the original data before doing the aggregation:

SELECT title, group_concat(id), group_concat(name)
FROM (SELECT title, id, name
      FROM ...
      ORDER BY title, id  -- or whatever order you want
     )
GROUP BY title;

In theory, it is possible that a future version of SQLite optimizes individual group_concat() calls by reading the values from an index, or something like that. But because that would indeed change the behaviour, it's unlikely. In any case, the behaviour of the same SQLite version will not change.

Thanks for your answer. Just to clarify, I don't care about the specific order at all, I only care that the order of `group_concat(id)` is the same order as `group_concat(name)`. In your example, does the sub-query only apply to `group_concat(name)` and not `group_concat(id)`? Is it guaranteed that the row order for `id` is the same as the row order for `name`? — sqlfused, Jun 24 '17 at 18:56

In SQLite, will multiple colums order the same way with group_concat?

1 Answers1