How Can I Order Results Within GROUP_CONCAT Using ORDER BY?

Ordering Results in GROUP_CONCAT with ORDER BY

In a situation where you have a table with multiple rows per client, and you desire to group and concatenate the data in a specific order, GROUP_CONCAT can be utilized with the ORDER BY clause. Let's consider a table named li containing the following data:

client_id	views	percentage
1	6	20
1	4	55
1	9	56
1	2	67
1	7	80
1	5	66
1	3	33
1	8	34
1	1	52

Initially, a simple GROUP_CONCAT query was used:

SELECT li.client_id, group_concat(li.views) AS views, group_concat(li.percentage) FROM li GROUP BY client_id;

This resulted in unordered concatenation:

client_id	views	group_concat(li.percentage)
1	6,4,9,2,7,5,3,8,1	20,55,56,67,80,66,33,34,52

To order the views in ascending order, we can use GROUP_CONCAT with ORDER BY as follows:

SELECT li.client_id,
  group_concat(li.views ORDER BY li.views ASC) AS views,
  group_concat(li.percentage ORDER BY li.views ASC) AS percentage
FROM li GROUP BY client_id

This will return the results in the desired order:

client_id	views	percentage
1	1,2,3,4,5,6,7,8,9	52,67,33,55,66,20,80,34,56

The above is the detailed content of How Can I Order Results Within GROUP_CONCAT Using ORDER BY?. For more information, please follow other related articles on the PHP Chinese website!