Ordering in a MySQL GROUP_CONCAT with a function in it

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?

 Answers

If anyone cares, I think I found a solution for at least a similar problem.

Solution

SELECT GROUP_CONCAT(columnName order by someColumn SEPARATOR '|') 

from tableName where fieldId = p.id

The order by goes in the group_concat BEFORE the separator if there is one.

---

I don't know of a standard way to do this. This query works, 

but I'm afraid it just depends on some implementation detail:

SELECT a_name, group_concat(b_id)
FROM (
    SELECT a.name AS a_name, b.id AS b_id
    FROM tbl1 a, tbl2 b
    ORDER BY a.name, b.id) a
GROUP BY a_name

---

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;