CODEWITHSUNDEEP
mysqlgroup-concatarrangeoverride
TVA van Hesteren
TVA van Hesteren

Reputation: 1241

MySQL GROUP_CONCAT Rearranges values

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?

Upvotes: 0

Views: 392

Answers (2)

SQL.RK
SQL.RK

Reputation: 157

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 1

Related Questions