[mysql] GROUP_CONCAT ORDER BY

I've a table like:

+-----------+-------+------------+
| 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 |

I tried group_concat:

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

+-----------+-------------------+-----------------------------+
| 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  |
+-----------+-------------------+-----------------------------+

But I want to get the views in order, like:

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

This question is related to mysql sql-order-by group-concat

The answer is


In IMPALA, not having order in the GROUP_CONCAT can be problematic, over at Coders'Co. we have some sort of a workaround for that (we need it for Rax/Impala). If you need the GROUP_CONCAT result with an ORDER BY clause in IMPALA, take a look at this blog post: http://raxdb.com/blog/sorting-by-regex/


The group_concat supports its own order by clause

http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/

So you should be able to write:

SELECT li.clientid, group_concat(li.views order by views) AS views,
group_concat(li.percentage order by percentage) 
FROM table_views GROUP BY client_id

You can use SEPARATOR and ORDER BY inside the GROUP_CONCAT function in this way:

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

Try

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

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function%5Fgroup-concat


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql-order-by

Laravel Eloquent: Ordering results of all() SQL for ordering by number - 1,2,3,4 etc instead of 1,10,11,12 SQL ORDER BY multiple columns How to use Oracle ORDER BY and ROWNUM correctly? MySQL order by before group by Ordering by specific field value first MySQL ORDER BY multiple column ASC and DESC How can I get just the first row in a result set AFTER ordering? SQL order string as number Order by multiple columns with Doctrine

Examples related to group-concat

How to use GROUP_CONCAT in a CONCAT in MySQL GROUP_CONCAT ORDER BY GROUP_CONCAT comma separator - MySQL MySQL DISTINCT on a GROUP_CONCAT() MySQL and GROUP_CONCAT() maximum length Postgresql GROUP_CONCAT equivalent? Can I concatenate multiple MySQL rows into one field? How to concatenate text from multiple rows into a single text string in SQL server?