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.

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).

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}