Ordering Values in a GROUP_CONCAT Statement

In MySQL, GROUP_CONCAT combines values from one column based on a specific grouping. However, the order of the concatenated values is typically determined by the order in which the grouping occurs. To control the sorting of these values, a specific syntax is required.

To sort the concatenated values in a GROUP_CONCAT statement, use an ORDER BY clause within the nested query. For example, consider the following query:

GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") 
FROM test_competence AS node, test_competence AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.id = l.competence 
  AND parent.id != 1 
ORDER BY parent.lft) SEPARATOR "<br />\n") AS competences

To sort the concatenated competence values in ascending order, modify the query as follows:

GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") 
FROM test_competence AS node, test_competence AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.id = l.competence 
  AND parent.id != 1 
ORDER BY parent.lft ASC) SEPARATOR "<br />\n") AS competences

Similarly, to sort the values in descending order, use a DESC keyword after ASC:

GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR " » ") 
FROM test_competence AS node, test_competence AS parent 
WHERE node.lft BETWEEN parent.lft AND parent.rgt 
  AND node.id = l.competence 
  AND parent.id != 1 
ORDER BY parent.lft DESC) SEPARATOR "<br />\n") AS competences