Ordering in a MySQL GROUP_CONCAT with a function in it

acme picture acme · Oct 16, 2009 · Viewed 20.8k times · Source

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?

Answer

Jazzy picture Jazzy · Nov 3, 2012

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.