How to use order by along with group_concat

Question

I have the following data in database

ID   |  AId            | field           | Value        | internalOrder |
-------------------------------------------------------------------------
| 86 |             193 | h1              | v1           |             1 |
| 43 |             193 | default         | default      |             2 |

I want to get concatenated field,value and internalOrder sorted by internalOrder groupedBy Aid. So that the results should be like

193 | h1,default | v1,default | 1,2

I have tried few things.

select Aid,group_concat(field), group_concat(value), group_concat(internalOrder order by internalOrder ASC) from table1 group by Aid order by Aid;

This produces results like this:

|193 | default,h1 | default,v1 | 1,2

Which is wrong.

How to produce the desired results?

you can check the question here http://stackoverflow.com/questions/8631210/group-concat-order-by — Santosh Jagtap, Jun 25 '15 at 10:22
`group_concat(internalOrder order by ID desc)` and same for other perhaps ? — Abhik Chakraborty, Jun 25 '15 at 10:23

score 1 · Answer 1 · answered Jun 25 '15 at 10:34

You need to include the order by in all the group_concat() operations. This is a local "order by". It doesn't apply to the whole query:

select Aid, group_concat(field order by internalOrder) as fields, 
       group_concat(value order b internalOrder) as values,
       group_concat(internalOrder order by internalOrder ASC) as internalOrders
from table1
group by Aid order by Aid;

Also note that this is separate from the order by on the query itself. That order by does not have a consistent effect on the order in the group_concat().

score 1 · Accepted Answer · answered Jun 25 '15 at 10:34

1

try below-

select Aid,group_concat(field order by internalOrder ASC), 
group_concat(value order by internalOrder ASC), 
group_concat(internalOrder order by internalOrder ASC) 
from table1 group by Aid order by Aid;

answered Jun 25 '15 at 10:34

Zafar Malik

6,734
2
19
30

score 0 · Answer 3 · answered Jun 25 '15 at 10:24

0

I think these sql will useful to you.

select Aid,group_concat(field order by field), 
group_concat(value order by value), 
group_concat(internalOrder order by internalOrder ASC) 
from table1 group by Aid order by Aid;

Thank you..

answered Jun 25 '15 at 10:24

Venkatesh Panabaka

2,064
4
19
27

Although he doesn't make it clear, I think what they want is to order all grouped values by `internalOrder`, whereas your code will order each grouped value by itself. – Eborbob Jun 25 '15 at 10:27

How to use order by along with group_concat

3 Answers3