SQLITE ordering group_concat with a left join

Question

I have the following query

        let Query = "SELECT LEVELS1.ID, LEVELS1.NAME, GROUP_CONCAT(FRAMEWORKLEVELS1.NAME,'&/?') AS DATA FROM LEVELS1 LEFT JOIN FRAMEWORKLEVELS1 ON LEVELS1.ID = FRAMEWORKLEVELS1.LEVELID WHERE FRAMEWORKID = :ID GROUP BY FRAMEWORKID"

which produces the following

DATA = "Level1&/?Level4&/?Level2&/?Level3";
ID = 1;
NAME = "Title";

What i want to do though is order the GROUP_CONCAT but how do i do this in SQLITE?

I have found the following link

Sqlite group_concat ordering

but i can't work out how to get the left join in there

Thanks

Possible duplicate of [Sqlite group\_concat ordering](https://stackoverflow.com/questions/1897352/sqlite-group-concat-ordering) — szmate1618, Jul 17 '19 at 13:19

score 1 · Accepted Answer · answered Nov 13 '15 at 09:25

Just get all the data you want, sort it, and then aggregate it:

SELECT ID,
       Name,
       group_concat(LevelName, '&/?') AS Data
FROM (SELECT Levels1.ID AS ID,
             Levels1.Name AS Name,
             FrameworkLevels1.Name AS LevelName
      FROM Levels1
      LEFT JOIN FrameworkLevels1 ON Levels1.ID = FrameworkLevels1.LevelID
      WHERE FrameworkID = :ID
      ORDER BY ...);

SQLITE ordering group_concat with a left join

1 Answers1