GROUP_CONCAT

Syntax

GROUP_CONCAT(expr)

Description

This function returns a string result with the concatenated non-NULL values from a group. If any expr in GROUP_CONCAT evaluates to NULL, that tuple is not present in the list returned by GROUP_CONCAT.

It returns NULL if all arguments are NULL, or there are no matching rows.

The maximum returned length in bytes is determined by the group_concat_max_len server system variable, which defaults to 1M.

If group_concat_max_len <= 512, the return type is VARBINARY or VARCHAR; otherwise, the return type is BLOB or TEXT. The choice between binary or non-binary types depends from the input.

The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val]
             [LIMIT {[offset,] row_count | row_count OFFSET offset}])

DISTINCT eliminates duplicate values from the output string.

ORDER BY determines the order of returned values.

SEPARATOR specifies a separator between the values. The default separator is a comma (,). It is possible to avoid using a separator by specifying an empty string.

LIMIT

The LIMIT clause can be used with GROUP_CONCAT. This was not possible prior to MariaDB 10.3.3.

Examples

SELECT student_name,
       GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

Get a readable list of MariaDB users from the mysql.user table:

SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
   FROM mysql.user;

In the former example, DISTINCT is used because the same user may occur more than once. The new line () used as a SEPARATOR makes the results easier to read.

Get a readable list of hosts from which each user can connect:

SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ') 
   FROM mysql.user GROUP BY User ORDER BY User;

The former example shows the difference between the GROUP_CONCAT's ORDER BY (which sorts the concatenated hosts), and the SELECT's ORDER BY (which sorts the rows).

From MariaDB 10.3.3, LIMIT can be used with GROUP_CONCAT, so, for example, given the following table:

CREATE TABLE d (dd DATE, cc INT);

INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);

the following query:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3                                                               |
+----------------------------------------------------------------------------+

can be more simply rewritten as:

SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3                                                |
+-------------------------------------------------------------+

NULLS:

CREATE OR REPLACE TABLE t1 (a int, b char);

INSERT INTO t1 VALUES (1, 'a'), (2, NULL);

SELECT GROUP_CONCAT(a, b) FROM t1;
+--------------------+
| GROUP_CONCAT(a, b) |
+--------------------+
| 1a                 |
+--------------------+

See Also

• CONCAT()

• CONCAT_WS()

• SELECT

• ORDER BY

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}