Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
GROUP_
On this page
Concatenates all of the values passed in during aggregation.
Note
This aggregate function is not to be confused with CONCAT, which is a non-aggregate function returning a concatenation of its list of arguments.
Syntax
GROUP_CONCAT( [DISTINCT]
expression, [ expression, [expression ...]]
[ORDER BY expression [ASC|DESC]]
[SEPARATOR string] )Arguments
-
DISTINCT: optionally filter the result set for only unique sets of expressions
-
expression: any set of expressions.
This may be a column name, the result of another function, or a math operation. -
ORDER BY: optionally sort the result set of the GROUP_
CONCAT values in ascending or descending order. By default, the result is sorted in ascending order. -
SEPARATOR string: by default, the separator character is the comma
,
.This argument overrides the default.
Note: You cannot use the DISTINCT keyword with the ORDER BY clause in a GROUP_ function.
Return Type
The return type is a string.group_ controls the maximum length string this function can return.
For more information on this variable, see the List of Engine Variables.
Examples
CREATE TABLE gc_products (id int, region varchar(30), ship_code varchar(5));INSERT INTO gc_products VALUES(1, 'North', 'A'),(2, 'North', 'B'),(3, 'South', 'A'),(4, 'East', 'A'),(5, 'East', 'C'),(6, 'West', 'D'),(7, 'South', 'A'),(8, 'West', 'B');
SELECT GROUP_CONCAT(ship_code) FROM gc_products;
+-------------------------+
| GROUP_CONCAT(ship_code) |
+-------------------------+
| A,B,A,A,C,D,A,B |
+-------------------------+SELECT GROUP_CONCAT(DISTINCT ship_code) AS distinct_codes FROM gc_products;
+----------------+
| distinct_codes |
+----------------+
| A,B,C,D |
+----------------+SELECT GROUP_CONCAT(DISTINCT ship_code SEPARATOR " ") AS distinct_codes FROM gc_products;
+----------------+
| distinct_codes |
+----------------+
| A B C D |
+----------------+SELECT GROUP_CONCAT(first_name, " ", last_name) FROM bffs;
+------------------------------------------+
| GROUP_CONCAT(first_name, " ", last_name) |
+------------------------------------------+
| Cecelia Cruz,Ari Floo,Skylar Rhodes |
+------------------------------------------+SELECT dept_id, GROUP_CONCAT(emp_id ORDER BY emp_id) FROM grptable GROUP_BY dept_id;
+---------+--------------------------------------+
| dept_id | GROUP_CONCAT(emp_id ORDER BY emp_id) |
+---------+--------------------------------------+
| 1 | 100 |
| 5 | 500,700 |
| 4 | 300,400 |
| 6 | 600 |
+---------+--------------------------------------+CREATE TABLE gc_emp_info(emp_id INT, dept_code INT, salary_level INT);INSERT INTO gc_emp_info VALUES(298, 401, 5), (341, 202, 2), (491, 111, 1);
SELECT GROUP_CONCAT(CONCAT(emp_id, '-', dept_code, '-', salary_level) ORDER BY emp_id DESC, dept_code ASC) FROM gc_emp_info;
+----------------------------------------------------------------+
| GROUP_CONCAT(CONCAT(emp_id, '-', dept_code, '-', salary_level) |
| ORDER BY emp_id DESC, dept_code ASC) |
| GROUP_CONCAT(first_name, " ", last_name) |
+----------------------------------------------------------------+
| 491-111-1,341-202-2,298-401-5 |
+----------------------------------------------------------------+Related Topics
-
JSON_
AGG can be used for similar tasks and also supports ORDER BY.
Last modified: June 16, 2023