How to Order GROUP_CONCAT() Values in MySQL

When performing a GROUP_CONCAT() in a MySQL query you might notice that the results come back in no particular order, or to be precise, in the same order as if you ran the query without the GROUP_CONCAT().

Let’s take the following SQL to create our test scenario:

1. CREATE TABLE `animals` (  
2.   `animal` varchar(10)  
3. ) ENGINE=MyISAM;  
4.   
5. INSERT INTO `animals` (`animal`) VALUES  
6. ('Dog'),  
7. ('Cat'),  
8. ('Rabbit'),  
9. ('Horse'),  
10. ('Aardvark');  

As you can see, we’ve got one table called ‘animals’ which contains five different types of animal.

Now, let’s imagine we want to get all the animals out of this table using a GROUP_CONCAT(). We can do something like so:

1. SELECT GROUP_CONCAT(`animal`) FROM `animals`;  
2.   
3. // Returns: Dog,Cat,Rabbit,Horse,Aardvark  

But what if we want to order these alphabetically? You might think the following would work:

1. SELECT GROUP_CONCAT(`animal`) FROM `animals` ORDER BY `animal`;  
2.   
3. // Returns: Dog,Cat,Rabbit,Horse,Aardvark  

Hmmmm… doesn’t look very ordered does it?!

The Solution

The way to order the results is to add an ORDER BY within the GROUP_CONCAT() function itself. Allow me to demonstrate:

view plaincopy to clipboardprint?

1. SELECT GROUP_CONCAT(`animal` ORDER BY `animal`) FROM `animals`;  
2.   
3. // Returns: Aardvark,Cat,Dog,Horse,Rabbit