GROUP_CONCAT ORDER BY

Question

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

are the values of views and percentage in the table stored as comma separated values? — Virendra, Dec 25 '11 at 19:44
No they aren't stored like that but I made them by my query i.e., with group_concat — ronquiq, Dec 25 '11 at 19:46
possible duplicate of [MySQL: Sort GROUP\_CONCAT values](http://stackoverflow.com/questions/995373/mysql-sort-group-concat-values) — outis, Jan 30 '13 at 22:38

aleroot · Accepted Answer · 2016-02-16T09:20:31.240

368

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

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

edited Feb 16 '16 at 09:20

answered Dec 25 '11 at 19:51

aleroot

66,082
27
164
205

12

shouldn't it be `group_concat(li.percentage ORDER BY li.views ASC)` so that the percentages come out in the order that matches the views, as requested by the original post? – Kai Carver Jun 17 '14 at 13:30
@aleroot, Is this query MySQL specific? – Pacerier Apr 30 '15 at 20:24
Yes, GROUP_CONCAT is a MySQL only function, however a similar result can be achieved on other database with a surrogate function ... – aleroot Apr 30 '15 at 20:41
3

If ordering the group_concat by the same expression you're group_concat()ing, you can just write `group_concat(li.views ORDER BY 1)`. Easiest! – Doin Dec 08 '15 at 11:39
@KaiCarver comment is true above query doesnt give the right response – iec2011007 Feb 16 '16 at 09:18
@iec2011007 I've adjusted the response, but when I answer questions here, I'm usually writing fast with the aim to give you the hint to solve the problem, then anyone can adapt the solution to his own context, isn't it fair enough ? – aleroot Feb 16 '16 at 09:22
Chill.. I was just pointing so that if anyone just copy pastes the snippit doesnt run into error. – iec2011007 Feb 16 '16 at 09:26
Thx @aleroot so handy this order by inside the group concat – Lukas Lukac Dec 27 '16 at 13:20
2

in case you are also using separator : `SELECT GROUP_CONCAT(CONCAT(u.RegionName,'-',u.UserName) ORDER BY u.RegionName SEPARATOR '@@')` – Himalaya Garg Mar 06 '18 at 07:37
Can we add limit in group_concat ? – vikas95prasad Nov 06 '19 at 12:31

TetonSig · Answer 2 · 2011-12-25T19:53:33.623

18

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

edited Dec 25 '11 at 19:53

answered Dec 25 '11 at 19:44

TetonSig

2,029
14
21

score 12 · Answer 3 · edited Feb 22 '19 at 07:39

12

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

edited Feb 22 '19 at 07:39

djm.im

2,652
3
24
40

answered Dec 25 '11 at 19:49

Virendra

2,480
3
21
36

score 2 · Answer 4 · answered Jun 06 '16 at 16:30

2

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/

answered Jun 06 '16 at 16:30

JMS

29
1

New link should be https://www.codersco.com/blog/2016/06/sort-by-regex/ – John Mar 26 '19 at 00:34

score 0 · Answer 5 · answered Nov 26 '20 at 12:12

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;

GROUP_CONCAT ORDER BY

5 Answers5

Linked

Related