Concatenate strings from a group. This function joins non-NULL values from multiple rows into a single string, with an optional separator.
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 server system variable, which defaults to 1M.
If group_concat_max_len <= 512, the return type is or ; otherwise, the return type is or . The choice between binary or non-binary types depends from the input.
The full syntax is as follows:
DISTINCT eliminates duplicate values from the output string.
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.
The clause can be used with GROUP_CONCAT.
Get a readable list of MariaDB users from the table:
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:
The former example shows the difference between the GROUP_CONCAT's (which sorts the concatenated hosts), and the SELECT's (which sorts the rows).
can be used with GROUP_CONCAT, so, for example, given the following table:
the following query:
can be more simply rewritten as:
NULLS:
This page is licensed: GPLv2, originally from
GROUP_CONCAT(expr)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}])SELECT student_name,
GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
FROM mysql.user;SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ')
FROM mysql.user GROUP BY User ORDER BY User;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);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 |
+----------------------------------------------------------------------------+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 |
+-------------------------------------------------------------+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 |
+--------------------+