Order by inside Group_concat

Question

This is my fiddle.

Table and data is

create table Table3 (MatchID varchar(10), ItemType varchar(10));
insert into Table3 values
('M001','Fruit'),
('M001','Animal'),
('M002','Fruit'),
('M002','Vehicle');

When you have a select query orderd by MatchID and ItemType , it is returning

select MatchID,ItemType from Table3 order by MatchID,ItemType;

    MATCHID ITEMTYPE
    M001    Animal
    M001    Fruit
    M002    Fruit
    M002    Vehicle

like this, which is expected and correct.

However when I group_concated, it is not returning in an ordered manner.

Select group_concat(ItemType) as typesTomatch ,MatchID
from (select MatchID,ItemType from Table3 
      order by MatchID,ItemType)
c group by MatchID;

It is returning

TYPESTOMATCH    MATCHID
Fruit,Animal    M001
Fruit,Vehicle   M002

against expected

TYPESTOMATCH    MATCHID
Animal,Fruit    M001
Fruit,Vehicle   M002

. Why group_concat behaves so? How to produce the expected output?

Answer 1

Try ORDER BY inside GROUP_CONCAT()

SELECT GROUP_CONCAT(ItemType ORDER BY ItemType) AS typesTomatch , MatchID
FROM Table3 GROUP BY MatchID;

See this SQLFiddle