MySQL GROUP_CONCAT Rearranges values

Question

Suppose I have the following table:

ID|Col1    |Col2
1 |Test1   |Test12
2 |Test2   |Test22
2 |Test3   |Test32

When I use a query like:

SELECT GROUP_CONCAT(Col1) as First, GROUP_CONCAT(Col2) as Second WHERE ID=2 GROUP BY ID

It sometimes returns the GROUP_CONCAT's rearranged. For example:

ID|First      |Second
2 |Test2,Test3|Test32,Test22

While I would expect it to return:

ID|First      |Second
2 |Test2,Test3|Test22,Test32

As you can see, it switched the concatenated values in the column named 'Second'. How can I prevent this from happening?

Use `GROUP_CONCAT(Col2 ORDER BY Col2)`. Order returned by `GROUP_CONCAT` is *arbitray* as there is no inherent order in an SQL table. — Giorgos Betsos, Mar 31 '17 at 11:59
@BoundaryImposition I was assuming the GROUP_CONCAT would return it by appending the found rows like row1, row2, row3 etc. But apparently it doesn't — TVA van Hesteren, Mar 31 '17 at 13:51
That _is_ what it does. But you did not specify an ordering. — Lightness Races in Orbit, Mar 31 '17 at 13:53
@BoundaryImposition, it doesn't at least not always. Because then it wouldn't have returned the structure like it did in my example — TVA van Hesteren, Mar 31 '17 at 13:55
Yes, it does, and it did. But you did not specify an ordering. You're assuming that your data was sorted/ordered to begin with. It was not. Because you did not specify an ordering. These are not arrays or strings that you are dealing with here -- they are data relations. — Lightness Races in Orbit, Mar 31 '17 at 13:57

score 1 · Answer 1 · answered Mar 31 '17 at 11:59

1

If you want the values in a particular order, then use order by:

SELECT GROUP_CONCAT(Col1 ORDER BY Col1) as First,
       GROUP_CONCAT(Col2 ORDER BY col2) as Second
FROM t
WHERE ID = 2
GROUP BY ID;

In general, SQL deals with unordered sets. If you want things in a particular order, then you need to explicitly specify the ordering.

answered Mar 31 '17 at 11:59

Gordon Linoff

This will only work assuming that the values within the columns have the same ordening structure like A-B-C A-B-C. When it is A-B-C B-A-C it will fail right? – TVA van Hesteren Mar 31 '17 at 13:51
@TVAvanHesteren . . . This will return the values in a canonical ordering -- alphabetically. If you have another column that specifies the ordering, you can use that in the `ORDER BY`. – Gordon Linoff Mar 31 '17 at 23:01
What I want to achieve is that the CONCAT_GROUP Col2 returns the values in the same order as the CONCAT_GROUP Col1 which I can't achieve so far. I tried ordening both on Col1, both on their own list so Col2 by Col2, Col1 by Col1. But still it won't match the found rows in indexes of the returned groups – TVA van Hesteren Apr 01 '17 at 10:23

score 1 · Answer 2 · edited May 09 '17 at 16:31

1

This table is missing common column (PK) to sort in group_concat.

ID|GroupID|Col1 |Col2

1|1 |Test1 |Test12

2|2 |Test2 |Test22

3|2 |Test3 |Test32

SELECT GROUP_CONCAT(Col1 ORDER BY ID) as First,
       GROUP_CONCAT(Col2 ORDER BY ID) as Second
FROM t
WHERE GroupID = 2
GROUP BY GroupID;

This will retain the order of group concat of col1 and col2.

edited May 09 '17 at 16:31

Funk Forty Niner

answered Mar 31 '17 at 12:13

SQL.RK

Yes it will, however I don't have the option to add another column. +1 for the order by within the GROUP_CONCAT though, didn't realized that it was possible – TVA van Hesteren Mar 31 '17 at 13:50

2 Answers2