Will this SQLite GROUP_CONCAT() order by work?

Question

I did some tests on my db (SQLite 3.21.0) and its seems that my solution works, but I am afraid that its just luck and it may fail in some cases, I would like to know if my solution is valid...

It seems like GROUP_CONCAT() using primary key or if there is no primary key it will try to find numeric column and will use it for the concatenation order, so I wanted to check my theory and decided to use WITH clause to generate "new" table, this way I will set the order that GROUP_CONCAT will have to use

Here is what I did: (simplified query)

WITH data AS (SELECT cID,
  pType || ':' || pID || ':' || pTotal || ':' || 
  (CASE WHEN pDate IS NULL OR pDate = '' THEN '0' ELSE pDate END) fees
  FROM Pay2015 AS A WHERE cID = A.cID AND pType > 1 ORDER BY pType)

SELECT A.cID, GROUP_CONCAT(data.fees, '|') fees FROM Pay2015 AS A
LEFT OUTER JOIN data ON A.cID = data.cID
WHERE A.cID = 98 AND pType = 0

WITH data will have table with first column as cID and it will be 98 for every row, it will be used in join, GROUP_CONCAT will also have to use it because its the only numeric column, and ORDER BY will set rows order that I need

Now the main query will execute GROUP_CONCAT on this new table (data) and because data.cID is the same for every row it will concatenate it as it

For comparison here is regular query that has the order problem:

SELECT cID, GROUP_CONCAT(pType || ':' || pID || ':' || pTotal || ':' || 
(CASE WHEN pDate IS NULL OR pDate = '' THEN '0' ELSE pDate END), '|') fees
FROM Pay2015 AS A WHERE cID = 98 AND pType > 1 ORDER BY pType

Pay2015 table data: (pID is primary key)

And this is the results when ordering by pType: (I use | to split by, removed all columns but pType for simplicity)

As you can see, the results order is the same as pID order in regular query

What do you think?

From the documentation for `group_concat()`: " The order of the concatenated elements is arbitrary. " — Shawn, Oct 20 '18 at 14:39
Possible duplicate of [Sqlite group\_concat ordering](https://stackoverflow.com/questions/1897352/sqlite-group-concat-ordering) — szmate1618, Jul 17 '19 at 13:19

score 0 · Answer 1 · answered Oct 21 '18 at 11:54

The implementation of group_concat() processes the rows in whatever order the database happens to read them.

The only way to enforce an order is to read the rows from a subquery (or view, or CTE) that actually has a specified order:

SELECT ... group_concat(...) ... FROM (SELECT ... ORDER BY ...);

Please note that that subquery must be the only data source in the FROM clause; if you are joining it with any other table, the join might cause the database to read rows in some other order. If you need to join, you must do it inside the subquery.

Will this SQLite GROUP_CONCAT() order by work?

1 Answers1