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?
If anyone cares, I think I found a solution for at least a similar problem.
http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/
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.