CODEWITHSUNDEEP
mysql
starkk92
starkk92

Reputation: 5924

How to use order by along with group_concat

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?

Upvotes: 0

Views: 254

Answers (3)

Zafar Malik
Zafar Malik

Reputation: 6844

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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().

Upvotes: 1

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

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

Upvotes: 0

Related Questions