mysql multiple group_concat order preservation

Question

let's say I have a table mytable:

select * from mytable:

+------+------+------+ | a | b | c | +------+------+------+ | 1 | 1 | a | | 1 | 2 | b | +------+------+------+ I want to group both the columns b and c with group_concat function:

select a, group_concat(b), group_concat(c) from mytable group by a

+------+-----------------+-----------------+ | a | group_concat(b) | group_concat(c) | +------+-----------------+-----------------+ | 1 | 1,2 | a,b | +------+-----------------+-----------------+ My question is, do the mysql guarantee, that the group_concat will be always in the same order, that I won't get results like 1, 2 in the second column and b, a in the third column?

score 7 · Accepted Answer · answered Jul 05 '14 at 12:36

7

You can use an ORDER BY clause inside the GROUP_CONCAT function:

SELECT a, 
    GROUP_CONCAT(b ORDER BY b), 
    GROUP_CONCAT(c ORDER BY c)
FROM mytable GROUP BY a

answered Jul 05 '14 at 12:36

Robby Cornelissen

91,784
22
134
156

score 4 · Answer 2 · answered Jul 05 '14 at 12:45

Without specifying a order by the orders are not guaranteed and that's the same reason group_concat() has it's own order by clause. Per MySQL Spec, the syntax is as below

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

so if you want to be sure about the order then specify a order by like

select a, 
group_concat(b order by b desc) as b_concat, 
group_concat(c order by c desc) as c_concat
from mytable 
group by a

mysql multiple group_concat order preservation

2 Answers2