How to Order GROUP_CONCAT() Values in MySQL

September 1st, 2012 - Posted by Steve Marks to MySQL, Web Development.

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)

INSERT INTO `animals` (`animal`) VALUES

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 Solution

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
This entry was posted on Saturday, September 1st, 2012 at 5:45 pm by +Steve Marks and is filed under MySQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.

Fear not, we won't publish this

Comments (0)

No comments have been left yet. Be the first