Ordering in a MySQL GROUP_CONCAT with a function in it

Viewed 21581

I want to order the results in a GROUP_CONCAT function. The problem is, that the selection in the GROUP_CONCAT-function is another function, like this (fantasy select):

SELECT a.name,
    GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY b.id ASC) AS course
FROM people a, stuff b, courses c
GROUP BY a.id

I want to get a result like (ordered by b.id):

michael    1:science,2:maths,3:physics

but I get:

michael    2:maths,1:science,3:physics

Does anyone know how I can order by b.id in my group_concat here?

6 Answers
Related