group_concat sqlite and order by

Question

I am trying to order within group_concat in sqlite3. This is not supported in the way that it is for mysql, so I CAN NOT do this:

group_concat(logID order by logDateTime DESC)

My full query is here, I need logID to be ordered by logDateTime. I read about a subquery method here: Sqlite group_concat ordering but I was unable to get it to work in my query.

SELECT logID,
       NULL AS sessionID,
       logDateTime,
       NULL AS sessionName,
       NULL AS NamesInRandomOrder
FROM logs
WHERE sessionID IS NULL

UNION ALL

SELECT NULL,
       sessions.sessionID,
       MAX(logDateTime),
       sessions.sessionName,
       group_concat(logID)
FROM logs
JOIN sessions ON logs.sessionID = sessions.sessionID
GROUP BY sessions.sessionID

ORDER BY 3 DESC

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

score 1 · Answer 1 · answered Feb 20 '13 at 10:21

This orders the subquery, but SQLite doesn't guarantee that the order remains in all cases:

SELECT logID,
       NULL AS sessionID,
       logDateTime,
       logName,
       NULL AS NamesInRandomOrder
FROM logs
WHERE sessionID IS NULL

UNION ALL

SELECT NULL,
       sessionID,
       MAX(logDateTime),
       sessionName,
       group_concat(logName)
FROM (SELECT sessions.sessionID AS sessionID,
             logDateTime,
             sessionName,
             logName
      FROM logs JOIN sessions ON logs.sessionID = sessions.sessionID
      ORDER BY sessions.sessionID,
               logName)
GROUP BY sessionID

ORDER BY 3 DESC

You again, thanks! A bit frustrating how close I am getting to what I need, only being held back by the limited nature of sqlite. Do you know of any alternative methods to ensure my logID's come back ordered by logDateTime. My final application will actually have several columns that need to be returned just like logID shown above, and all of those columns will need to be listed/ordered by logDateTime. — livinzlife, Feb 20 '13 at 21:04

group_concat sqlite and order by

1 Answers1