Ordering in a MySQL GROUP_CONCAT with a function in it
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.idThe order by goes in the group_concat BEFORE the separator if there is one.
I know this is really old, but just now I was looking for an answer and @korny's answer gave me the idea for this:
SELECT a.name,GROUP_CONCAT(DISTINCT CONCAT_WS(':', b.id, c.name) ORDER BY CONCAT_WS(':', b.id, c.name) ASC) AS courseFROM people a, stuff b, courses cGROUP BY a.id(And it works for me, if that wasn't clear :-) )
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) aGROUP BY a_name