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:
CREATE TABLE `animals` ( `animal` varchar(10) ) ENGINE=MyISAM; INSERT INTO `animals` (`animal`) VALUES ('Dog'), ('Cat'), ('Rabbit'), ('Horse'), ('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:
SELECT GROUP_CONCAT(`animal`) FROM `animals`; // Returns: Dog,Cat,Rabbit,Horse,Aardvark
But what if we want to order these alphabetically? You might think the following would work:
SELECT GROUP_CONCAT(`animal`) FROM `animals` ORDER BY `animal`; // Returns: Dog,Cat,Rabbit,Horse,Aardvark
Hmmmm… doesn’t look very ordered does it?!
The way to order the results is to add an ORDER BY within the GROUP_CONCAT() function itself. Allow me to demonstrate:
SELECT GROUP_CONCAT(`animal` ORDER BY `animal`) FROM `animals`; // Returns: Aardvark,Cat,Dog,Horse,Rabbit