MySQL – GROUP_CONCAT unique values

admin

May 8, 2012

[adsense id=”0514458240″ width=”468″ height=”60″]

GROUP_CONCAT allows you to return a list of values in a single field. Consider the following table:

mysql> select * from t1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |  100 |  115 | 
| ronan4 |  155 |  116 | 
| ronan4 |  225 |  250 | 
| ronan4 |  300 |  275 | 
| ronan4 |  325 |  350 | 
| ronan4 |    1 |    2 | 
| ronan4 |    1 |    3 | 
| t13    |    7 |    6 | 
+--------+------+------+
8 rows in set (0.00 sec)

Here we want to return a list of the users in col1.

mysql> select group_concat(col1) from t1;
+------------------------------------------------------+
| group_concat(col1)                                   |
+------------------------------------------------------+
| ronan4,ronan4,ronan4,ronan4,ronan4,ronan4,ronan4,t13 | 
+------------------------------------------------------+
1 row in set (0.00 sec)

So far so good but what if we want to remove the duplicate values in this list. In this case here we need to specify a DISTINCT inside the GROUP_CONCAT function.

mysql> select group_concat(distinct col1) from t1;
+-----------------------------+
| group_concat(distinct col1) |
+-----------------------------+
| ronan4,t13                  | 
+-----------------------------+
1 row in set (0.00 sec)

[googleplus]

MySQL