[SQLite-Tutorial] Wie bekomme ich eine Reihenfolge im GROUP_CONCAT hin

[Zvoni]

Mahlzeit.
In einem anderen Forum habe ich einem User ausgeholfen ein Problem mit SQLite zu lösen.
Der User hatte eine Tabelle "employee" und eine Tabelle "Tasks" welche per m:n-Beziehung über eine Tabelle "emp_tasks" verknüpft sind.
Der User wollte zu allen "employees" die Ihnen zugewiesenen Aufgaben abrufen, die Aufgaben jedoch in einem zusammenhängenden String
Beispiel:

employee	Task
John Smith	task1, task4, task6
Robert Miller	task2, task4, task6, task8

Die GROUP_CONCAT-Funktion von SQLite hat keinen ORDER BY-Parameter (wie aus anderen DBMS bekannt).
Die Dokumentation auf sqlite.org besagt sogar:

The order of the concatenated elements is arbitrary.

Bedeutet: Wörtlich übersetzt "willkürlich", wobei ich eher darauf tippe, die Reihenfolge ist dieselbe wie auch in der Tabelle selbst (also gemäss interner ROW_ID). Kann mich aber auch irren.

Ich habe daraufhin zwei Workarounds gefunden bzw. erarbeitet eine Reihenfolge in das GROUP_CONCAT zu bekommen
Anbei das Setup für eine Test-Datenbank in SQLite

CREATE TABLE "tbl_employee" (
    "ID"    INTEGER,
    "FirstName"    TEXT NOT NULL,
    "LastName"    TEXT NOT NULL,
    "FullName" TEXT GENERATED ALWAYS AS ("FirstName" || ' ' || "LastName") VIRTUAL,
    PRIMARY KEY("ID")
);
CREATE TABLE "tbl_task" (
    "ID"    INTEGER,
    "Description"    TEXT NOT NULL,
    PRIMARY KEY("ID")
);
CREATE TABLE "tbl_emp_task" (
    "emp_id"    INTEGER,
    "task_id"    INTEGER,
    FOREIGN KEY("task_id") REFERENCES "tbl_task"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("emp_id") REFERENCES "tbl_employee"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY("emp_id","task_id")
);
INSERT INTO tbl_employee ("ID", "FirstName", "LastName") VALUES (1, 'Tom', 'Brown'), (2, 'Robert', 'Miller'), (3, 'John', 'Smith');
INSERT INTO tbl_task ("ID", "Description") VALUES (1, 'answering phones'), (2, 'preparing meeting'), (3, 'receiving visitors'), (4, 'Maintaining security'), (5, 'Arranging couriers');
INSERT INTO tbl_emp_task ("emp_id", "task_id") VALUES (1,1), (1,2), (1,4), (2,2),(2,3),(2,5);

Und hier die zwei Varianten:

SELECT
    E.FullName,
    COALESCE(T.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
FROM tbl_employee AS E
LEFT JOIN
    (SELECT DISTINCT
        ET.emp_ID,
        GROUP_CONCAT(TA.Description) OVER(PARTITION BY ET.emp_ID ORDER BY Lower(TA.Description) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS AssignedTasks
    FROM tbl_emp_task AS ET
    INNER JOIN tbl_task AS TA
    ON TA.ID=ET.task_id) AS T
ON T.emp_id=E.ID
GROUP BY E.FullName ORDER BY E.LastName, E.FirstName

With CTE(EID,TID,TDESC) AS
    (SELECT
        ET.emp_ID,
        ET.task_id,
        T.Description
    FROM tbl_emp_task AS ET
    INNER JOIN tbl_task AS T
    ON T.ID=ET.task_id
    ORDER BY Lower(T.Description))
SELECT
    E.FullName,
    COALESCE(C.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
FROM tbl_employee AS E
LEFT JOIN
    (SELECT
        EID,
        GROUP_CONCAT(TDESC, ', ') AS AssignedTasks
    FROM (SELECT EID,TID,TDESC FROM CTE) GROUP BY EID) AS C
ON C.EID=E.ID
GROUP BY E.FullName ORDER BY E.LastName, E.FirstName

Die Reihenfolge ignoriert Gross/Kleinschreibung in "Description". Will man das nicht haben, einfach die Lower-Funktion entfernen

btw: Im Zuge meiner Recherche bin ich auf die GENERATED COLUMNS von SQLite gestossen.
Feines Feature.
Spart man sich den "Klassiker" das Zeug im SELECT-Statement zusammenzubauen

SELECT FirstName, LastName, (Firstname || ' ' || LastName) As FullName FROM tbl_employee

Viel Spass

EDIT: Vergessen:
Gilt ab SQLite Version 3.31.0 (2020-01-22)