Why does ordering report rows leave a concatenated workflow out of order?

Prepare the report input

Original steps have declared position 1 entry,2 review,3 finish. Outer ORDER BY orders group rows, not values fed into group_concat. Put position,id inside the aggregate ORDER BY. Without it, aggregate input order is not guaranteed.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE steps(id INTEGER PRIMARY KEY,group_code TEXT NOT NULL,position INTEGER NOT NULL,label TEXT NOT NULL); INSERT INTO steps VALUES(1,'A',2,'review'),(2,'A',1,'entry'),(3,'A',3,'finish');

Compare misleading and corrected results

SELECT group_code,group_concat(label,',') AS step_order FROM steps GROUP BY group_code ORDER BY group_code;

SELECT group_code,group_concat(label,',' ORDER BY position,id) AS step_order FROM steps GROUP BY group_code ORDER BY group_code;

This execution yields review,entry,finish in the bad result. The corrected string is entry,review,finish. The observed bad order is not a promise for every SQLite plan.

Check a fresh boundary case

Add archive at position four. It follows finish in the corrected export.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO steps VALUES(4,'A',4,'archive');
SELECT group_code,group_concat(label,',' ORDER BY position,id) AS step_order FROM steps GROUP BY group_code ORDER BY group_code;