187

In short: Is there any way to sort the values in a GROUP_CONCAT statement?

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

I get this row:

Crafts » Joinery

Administration » Organization

I want it like this:

Administration » Organization

Crafts » Joinery

Machavity
  • 28,730
  • 25
  • 78
  • 91
Ivar
  • 4,096
  • 6
  • 35
  • 50

2 Answers2

403

Sure, see http://dev.mysql.com/doc/refman/...tions.html#function_group-concat:

SELECT student_name,
  GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
  FROM student
  GROUP BY student_name;
Sampson
  • 251,934
  • 70
  • 517
  • 549
  • Your code is heavily relied upon for your specific answer, and therefore should not be placed anywhere but your original post. If you put it here in this comment, many programmers here won't see it, and you won't get the best possible response :) – Sampson Jun 15 '09 at 10:38
  • Sad but true. :) Is that code enough or should I write the whole query? – Ivar Jun 15 '09 at 10:42
  • Did you try ASC instead of DESC? – Sampson Jun 15 '09 at 10:52
  • I didn't use either of them (ASC = default). – Ivar Jun 15 '09 at 11:18
  • 12
    I didn't know you could `order by` within a `group_concat` until this exact moment. Solves a problem I was having. Thanks! – DiMono Jul 28 '16 at 21:34
24

Do you mean to order by?

SELECT _key,            
COUNT(*) as cnt,            
GROUP_CONCAT(_value ORDER BY _value SEPARATOR ', ') as value_list      
FROM group_concat_test      
GROUP BY _key      
ORDER BY _key;
mimoralea
  • 8,618
  • 5
  • 50
  • 55
Haim Evgi
  • 114,996
  • 43
  • 205
  • 218