[SQLite] How to get an Order within a GROUP_CONCAT

[Zvoni]

In another Forum i helped out an User who was struggling with the following:
He had a table "tbl_employee" and a table "tbl_task", which were connected in an "m:m"-scenario via "tbl_emp_task"

He wanted to query his SQLite-Database to show for all employees their assigned tasks, but the tasks as a single text, preferably in alphabetic order
Example

employee	task
John Smith	task1, task4, task6
Robert Miller	task3, task4, task5

The GROUP_CONCAT-Function of SQLite doesn't have a ORDER BY-Modifier.
The documenation on sqlite.org even says:

The order of the concatenated elements is arbitrary.

Though i think, the order is the order the elements are "presented", in case of a base-table my guess is the (hidden) row_id

Therefore i developed a solution as follows:
Setup for test-Database

CREATE TABLE "tbl_employee" (
    "ID"    INTEGER,
    "FirstName"    TEXT NOT NULL,
    "LastName"    TEXT NOT NULL,    
    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);

Note the combined Primary Key for tbl_emp_task consisting of the Foreign Keys.
That way we can make sure that each employee can be assigned a specific task only once


and here the solution:

With CTE(EID,TDESC) AS
    (SELECT
        ET.emp_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.FirstName || ' ' || E.LastName As FullName,
    COALESCE(C.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
FROM tbl_employee AS E
LEFT JOIN
    (SELECT
        EID,
        GROUP_CONCAT(TDESC, ', ') AS AssignedTasks
    FROM CTE GROUP BY EID) AS C
ON C.EID=E.ID
GROUP BY E.FirstName || ' ' || E.LastName ORDER BY E.LastName, E.FirstName

If you want Case-Sensitive order, remove the "Lower"-Function within the CTE

Try it out: https://dbfiddle.uk/?rdbms=sqlite_3....f7da10287c6885