[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

[wqweto]

Why do you need FROM (SELECT EID,TID,TDESC FROM CTE) GROUP BY EID instead of just FROM CTE GROUP BY EID?

cheers,
</wqw>

[Zvoni]

Why do you need FROM (SELECT EID,TID,TDESC FROM CTE) GROUP BY EID instead of just FROM CTE GROUP BY EID?

cheers,
</wqw>

you're right
It has to do with that i've started to explicitly select columns for JOINS, so call it personal preference
(And yes, TID as Output-Column for the CTE is not necessary. It's a leftover, when i was playing with it)

EDIT: I've edited my original post to reflect wqweto's comment as well as removed TID from the CTE, and replaced the link to the new fiddle

[wqweto]

Btw, sqlite lacks ORDER BY on GROUP_CONCAT but uses the *physical* order of the source data i.e. using FROM (SELECT * FROM CTE ORDER BY TDESC DESC) GROUP BY EID instead of FROM CTE GROUP BY EID reverses the order of concatenated texts so you probably would want to reverse the change above but add an explicit ORDER BY (sorry about that).

cheers,
</wqw>

[Zvoni]

Btw, sqlite lacks ORDER BY on GROUP_CONCAT but uses the *physical* order of the source data i.e. using FROM (SELECT * FROM CTE ORDER BY TDESC DESC) GROUP BY EID instead of FROM CTE GROUP BY EID reverses the order of concatenated texts so you probably would want to reverse the change above but add an explicit ORDER BY (sorry about that).

cheers,
</wqw>

I already quoted the SQLite-doc's, that there is an "arbitrary" Order for GROUP_CONCAT, though i also wrote my thoughts on it.

Would have to disagree with your proposal.
A CTE is basically a temp. VIEW, and the CTE itself is already ordering the Task-Descriptions case-insensitive (Lower-Function in the ORDER BY inside the CTE).
So the CTE is "presenting" the Data in a different order than the "physical" Data in the Base-Table, but since the GROUP_CONCAT's source is the CTE, and not the Base-table, the Order is correct.

Though, i wouldn't mind a Feedback of tests with a significiant amount of Data

And you have no reason to be sorry.