GROUP_CONCAT(expr)
This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
The maximum returned length in bytes is determined by the group_concat_max_len server system variable, which defaults to 1M (>= MariaDB 10.2.4) or 1K (<= MariaDB 10.2.3).
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])
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.
Until MariaDB 10.3.2, it was not possible to use the LIMIT clause with GROUP_CONCAT
. This restriction was lifted in MariaDB 10.3.3.
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 (\n
) 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 | +-------------------------------------------------------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/group_concat/