drop table if exists t1, t2; create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null); insert into t1 values (1,1,"a","a"); insert into t1 values (2,2,"b","a"); insert into t1 values (2,3,"c","b"); insert into t1 values (3,4,"E","a"); insert into t1 values (3,5,"C","b"); insert into t1 values (3,6,"D","b"); insert into t1 values (3,7,"d","d"); insert into t1 values (3,8,"d","d"); insert into t1 values (3,9,"D","c"); select grp,group_concat(c) from t1 group by grp; grp group_concat(c) 1 a 2 b,c 3 E,C,D,d,d,D explain extended select grp,group_concat(c) from t1 group by grp; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort Warnings: Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(`test`.`t1`.`c` separator ',') AS `group_concat(c)` from `test`.`t1` group by `test`.`t1`.`grp` select grp,group_concat(a,c) from t1 group by grp; grp group_concat(a,c) 1 1a 2 2b,3c 3 4E,5C,6D,7d,8d,9D select grp,group_concat("(",a,":",c,")") from t1 group by grp; grp group_concat("(",a,":",c,")") 1 (1:a) 2 (2:b),(3:c) 3 (4:E),(5:C),(6:D),(7:d),(8:d),(9:D) select grp,group_concat(c separator ",") from t1 group by grp; grp group_concat(c separator ",") 1 a 2 b,c 3 E,C,D,d,d,D select grp,group_concat(c separator "---->") from t1 group by grp; grp group_concat(c separator "---->") 1 a 2 b---->c 3 E---->C---->D---->d---->d---->D select grp,group_concat(c order by c) from t1 group by grp; grp group_concat(c order by c) 1 a 2 b,c 3 C,D,d,d,D,E select grp,group_concat(c order by c desc) from t1 group by grp; grp group_concat(c order by c desc) 1 a 2 c,b 3 E,D,d,d,D,C select grp,group_concat(d order by a) from t1 group by grp; grp group_concat(d order by a) 1 a 2 a,b 3 a,b,b,d,d,c select grp,group_concat(d order by a desc) from t1 group by grp; grp group_concat(d order by a desc) 1 a 2 b,a 3 c,d,d,b,b,a select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp; grp group_concat(a order by a,d+c-ascii(c)-a) 1 1 2 2,3 3 4,5,6,7,8,9 select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp; grp group_concat(a order by d+c-ascii(c),a) 1 1 2 3,2 3 7,8,4,6,9,5 select grp,group_concat(c order by 1) from t1 group by grp; grp group_concat(c order by 1) 1 a 2 b,c 3 C,D,d,d,D,E select grp,group_concat(distinct c order by c) from t1 group by grp; grp group_concat(distinct c order by c) 1 a 2 b,c 3 C,D,E select grp,group_concat(distinct c order by c desc) from t1 group by grp; grp group_concat(distinct c order by c desc) 1 a 2 c,b 3 E,D,C explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort Warnings: Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` DESC separator ',') AS `group_concat(distinct c order by c desc)` from `test`.`t1` group by `test`.`t1`.`grp` select grp,group_concat(c order by c separator ",") from t1 group by grp; grp group_concat(c order by c separator ",") 1 a 2 b,c 3 C,D,d,d,D,E select grp,group_concat(c order by c desc separator ",") from t1 group by grp; grp group_concat(c order by c desc separator ",") 1 a 2 c,b 3 E,D,d,d,D,C select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; grp group_concat(distinct c order by c separator ",") 1 a 2 b,c 3 C,D,E explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 9 Using filesort Warnings: Note 1003 select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` ASC separator ',') AS `group_concat(distinct c order by c separator ",")` from `test`.`t1` group by `test`.`t1`.`grp` select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp; grp group_concat(distinct c order by c desc separator ",") 1 a 2 c,b 3 E,D,C select grp,group_concat(c order by grp desc) from t1 group by grp order by grp; grp group_concat(c order by grp desc) 1 a 2 c,b 3 D,d,d,D,C,E select grp, group_concat(a separator "")+0 from t1 group by grp; grp group_concat(a separator "")+0 1 1 2 23 3 456789 select grp, group_concat(a separator "")+0.0 from t1 group by grp; grp group_concat(a separator "")+0.0 1 1.0 2 23.0 3 456789.0 select grp, ROUND(group_concat(a separator "")) from t1 group by grp; grp ROUND(group_concat(a separator "")) 1 1 2 23 3 456789 drop table t1; create table t1 (grp int, c char(10)); insert into t1 values (1,NULL),(2,"b"),(2,NULL),(3,"E"),(3,NULL),(3,"D"),(3,NULL),(3,NULL),(3,"D"),(4,""),(5,NULL); select grp,group_concat(c order by c) from t1 group by grp; grp group_concat(c order by c) 1 NULL 2 b 3 D,D,E 4 5 NULL set group_concat_max_len = 4; select grp,group_concat(c) from t1 group by grp; grp group_concat(c) 1 NULL 2 b 3 D,D, 4 5 NULL Warnings: Warning 1260 1 line(s) were cut by GROUP_CONCAT() show warnings; Level Code Message Warning 1260 1 line(s) were cut by GROUP_CONCAT() set group_concat_max_len = 1024; select group_concat(sum(c)) from t1 group by grp; ERROR HY000: Invalid use of group function select grp,group_concat(c order by 2) from t1 group by grp; ERROR 42S22: Unknown column '2' in 'order clause' drop table t1; create table t1 ( URL_ID int(11), URL varchar(80)); create table t2 ( REQ_ID int(11), URL_ID int(11)); insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); insert into t2 values (1,4), (5,4), (5,5); select REQ_ID, Group_Concat(URL) as URL from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID; REQ_ID URL 1 X 5 X,X,X select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll, Max(t1.URL_ID) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID; REQ_ID URL urll urlg 1 X 4 4 5 X,X,X 4 5 drop table t1; drop table t2; create table t1 (id int, name varchar(16)); insert into t1 values (1,'longername'),(1,'evenlongername'); select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1; without distinct: how it should be 1:longername,1:evenlongername select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1; with distinct: cutoff at length of shortname 1:longername,1:evenlongername drop table t1; create table t1(id int); create table t2(id int); insert into t1 values(0),(1); select group_concat(t1.id) FROM t1,t2; group_concat(t1.id) NULL drop table t1; drop table t2; create table t1 (bar varchar(32)); insert into t1 values('test1'),('test2'); select group_concat(bar order by concat(bar,bar)) from t1; group_concat(bar order by concat(bar,bar)) test1,test2 select group_concat(bar order by concat(bar,bar) desc) from t1; group_concat(bar order by concat(bar,bar) desc) test2,test1 select bar from t1 having group_concat(bar)=''; bar select bar from t1 having instr(group_concat(bar), "test") > 0; bar test1 select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0; bar test1 drop table t1; create table t1 (a int, a1 varchar(10)); create table t2 (a0 int); insert into t1 values (0,"a"),(0,"b"),(1,"c"); insert into t2 values (1),(2),(3); select group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1; group_concat(a1 order by (t1.a IN (select a0 from t2))) b,a,c select group_concat(a1 order by (t1.a)) from t1; group_concat(a1 order by (t1.a)) b,a,c drop table t1, t2; CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL); INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3); CREATE TABLE t2 (id1 tinyint(4) NOT NULL); INSERT INTO t2 VALUES (1),(2),(3),(4),(5); SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 AND t1.id1=1 GROUP BY t1.id1; id1 concat_id 1 1,2,3,4,5 SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; id1 concat_id 1 1,2,3,4,5 2 1,2,3 SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 DESC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; id1 concat_id 1 5,4,3,2,1 2 3,2,1 SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; id1 concat_id 1 5,4,3,2,1 2 3,2,1 SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; id1 concat_id 1 51,42,33,24,15 2 33,24,15 SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; id1 concat_id 1 51,42,33,24,15 2 33,24,15 SELECT t1.id1, GROUP_CONCAT(t1.id2,"/",6-t1.id2 ORDER BY 1+0,6-t1.id2,t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1; id1 concat_id 1 5/1,4/2,3/3,2/4,1/5 2 3/3,2/4,1/5 drop table t1,t2; create table t1 (s1 char(10), s2 int not null); insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4); select distinct s1 from t1 order by s2,s1; s1 c a b select group_concat(distinct s1) from t1; group_concat(distinct s1) a,b,c select group_concat(distinct s1 order by s2) from t1 where s2 < 4; group_concat(distinct s1 order by s2) c,b,a select group_concat(distinct s1 order by s2) from t1; group_concat(distinct s1 order by s2) c,b,a,c drop table t1; create table t1 (a int, c int); insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5); create table t2 (a int, c int); insert into t2 values (1, 5), (2, 4), (3, 3), (3,3); select group_concat(c) from t1; group_concat(c) 2,3,4,5 select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1; grp 5,4,3,2 select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1; grp 5,4,3,2 select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1; grp 2,4,3,5 select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1; a grp 1 2 2 4,3 3 5 select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1 group by 1; a grp 1 2 2 4,3 3 5 select t1.a, group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1 group by 1; a grp 1 2 2 4,3 3 5 select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp; a c grp 3 5 3,3 2 3 4 2 4 4 1 2 5 drop table t1,t2; CREATE TABLE t1 ( a int ); CREATE TABLE t2 ( a int ); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1), (2); SELECT GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) FROM t1, t2 GROUP BY t1.a; GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) 1,2 2,4 DROP TABLE t1, t2; CREATE TABLE t1 (a char(4)); INSERT INTO t1 VALUES ('John'), ('Anna'), ('Bill'); SELECT GROUP_CONCAT(a SEPARATOR '||') AS names FROM t1 HAVING names LIKE '%An%'; names John||Anna||Bill SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1 HAVING LEFT(names, 1) ='J'; names John###Anna###Bill DROP TABLE t1; CREATE TABLE t1 ( a int, b TEXT ); INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row'); SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a; GROUP_CONCAT(b ORDER BY b) First Row Second Row DROP TABLE t1; CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID)); INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ'); CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC)); INSERT INTO t2 VALUES (1,'A'),(1,'B'),(3,'F'); SELECT t1.A_ID, GROUP_CONCAT(t2.B_DESC) AS B_DESC FROM t1 LEFT JOIN t2 ON t1.A_ID=t2.A_ID GROUP BY t1.A_ID ORDER BY t1.A_DESC; A_ID B_DESC 1 A,B 2 NULL 3 F DROP TABLE t1; DROP TABLE t2; create table t1 (a int, b text); insert into t1 values (1, 'bb'), (1, 'ccc'), (1, 'a'), (1, 'bb'), (1, 'ccc'); insert into t1 values (2, 'BB'), (2, 'CCC'), (2, 'A'), (2, 'BB'), (2, 'CCC'); select group_concat(b) from t1 group by a; group_concat(b) bb,ccc,a,bb,ccc BB,CCC,A,BB,CCC select group_concat(distinct b) from t1 group by a; group_concat(distinct b) bb,ccc,a BB,CCC,A select group_concat(b order by b) from t1 group by a; group_concat(b order by b) a,bb,bb,ccc,ccc A,BB,BB,CCC,CCC select group_concat(distinct b order by b) from t1 group by a; group_concat(distinct b order by b) a,bb,ccc A,BB,CCC set local group_concat_max_len=4; select group_concat(b) from t1 group by a; group_concat(b) bb,c BB,C Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() select group_concat(distinct b) from t1 group by a; group_concat(distinct b) bb,c BB,C Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() select group_concat(b order by b) from t1 group by a; group_concat(b order by b) a,bb A,BB Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() select group_concat(distinct b order by b) from t1 group by a; group_concat(distinct b order by b) a,bb A,BB Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() insert into t1 values (1, concat(repeat('1', 300), '2')), (1, concat(repeat('1', 300), '2')), (1, concat(repeat('0', 300), '1')), (2, concat(repeat('1', 300), '2')), (2, concat(repeat('1', 300), '2')), (2, concat(repeat('0', 300), '1')); set local group_concat_max_len=1024; select group_concat(b) from t1 group by a; group_concat(b) bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 select group_concat(distinct b) from t1 group by a; group_concat(distinct b) bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 select group_concat(b order by b) from t1 group by a; group_concat(b order by b) 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,bb,ccc,ccc 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,BB,CCC,CCC select group_concat(distinct b order by b) from t1 group by a; group_concat(distinct b order by b) 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC set local group_concat_max_len=400; select group_concat(b) from t1 group by a; group_concat(b) bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111 BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111 Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() select group_concat(distinct b) from t1 group by a; group_concat(distinct b) bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() select group_concat(b order by b) from t1 group by a; group_concat(b order by b) 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() select group_concat(distinct b order by b) from t1 group by a; group_concat(distinct b order by b) 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() drop table t1; create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci, b varchar(255) character set koi8r); insert into t1 values ('xxx','yyy'); select collation(a) from t1; collation(a) cp1250_general_ci select collation(group_concat(a)) from t1; collation(group_concat(a)) cp1250_general_ci create table t2 select group_concat(a) as a from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` varchar(400) character set cp1250 default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select collation(group_concat(a,_koi8r'test')) from t1; collation(group_concat(a,_koi8r'test')) cp1250_general_ci select collation(group_concat(a,_koi8r 0xC1C2)) from t1; ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat' select collation(group_concat(a,b)) from t1; ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat' drop table t1; drop table t2; CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850); INSERT INTO t1 VALUES ('�'); SELECT a FROM t1; a � SELECT GROUP_CONCAT(a) FROM t1; GROUP_CONCAT(a) � DROP TABLE t1; CREATE TABLE t1 (id int); SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; gc NULL DROP TABLE t1; create table t2 (a int, b int); insert into t2 values (1,1), (2,2); select b x, (select group_concat(x) from t2) from t2; x (select group_concat(x) from t2) 1 1,1 2 2,2 drop table t2; create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int); insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3); select d,a,b from t1 order by a; d a b 1 1 3 2 1 4 3 1 2 5 1 1 6 1 2 4 2 7 7 2 3 8 2 3 explain select a, group_concat(b) from t1 group by a with rollup; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort select a, group_concat(b) from t1 group by a with rollup; a group_concat(b) 1 3,4,2,1,2 2 7,3,3 NULL 3,4,2,1,2,7,3,3 select a, group_concat(distinct b) from t1 group by a with rollup; a group_concat(distinct b) 1 3,4,2,1 2 7,3 NULL 3,4,2,1,7 select a, group_concat(b order by b) from t1 group by a with rollup; a group_concat(b order by b) 1 1,2,2,3,4 2 3,3,7 NULL 1,2,2,3,3,3,4,7 select a, group_concat(distinct b order by b) from t1 group by a with rollup; a group_concat(distinct b order by b) 1 1,2,3,4 2 3,7 NULL 1,2,3,4,7 drop table t1; create table t1 (a char(3), b char(20), primary key (a, b)); insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English'); select group_concat(a) from t1 group by b; group_concat(a) ABW ABW drop table t1; CREATE TABLE t1 ( aID smallint(5) unsigned NOT NULL auto_increment, sometitle varchar(255) NOT NULL default '', bID smallint(5) unsigned NOT NULL, PRIMARY KEY (aID), UNIQUE KEY sometitle (sometitle) ); INSERT INTO t1 SET sometitle = 'title1', bID = 1; INSERT INTO t1 SET sometitle = 'title2', bID = 1; CREATE TABLE t2 ( bID smallint(5) unsigned NOT NULL auto_increment, somename varchar(255) NOT NULL default '', PRIMARY KEY (bID), UNIQUE KEY somename (somename) ); INSERT INTO t2 SET somename = 'test'; SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') FROM t1 JOIN t2 ON t1.bID = t2.bID; COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') 2 test INSERT INTO t2 SET somename = 'test2'; SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') FROM t1 JOIN t2 ON t1.bID = t2.bID; COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') 2 test DELETE FROM t2 WHERE somename = 'test2'; SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') FROM t1 JOIN t2 ON t1.bID = t2.bID; COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') 2 test DROP TABLE t1,t2; select * from (select group_concat('c') from DUAL) t; group_concat('c') c create table t1 ( a int not null default 0); select * from (select group_concat(a) from t1) t2; group_concat(a) NULL select group_concat('x') UNION ALL select 1; group_concat('x') x 1 drop table t1; CREATE TABLE t1 (id int, a varchar(9)); INSERT INTO t1 VALUES (2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, ''); SELECT GROUP_CONCAT(a) FROM t1; GROUP_CONCAT(a) ,,x,y,z, SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; GROUP_CONCAT(a ORDER BY a) ,,,x,y,z SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id; GROUP_CONCAT(a) ,y ,x z, SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; GROUP_CONCAT(a ORDER BY a) ,y ,x ,z DROP TABLE t1; create table t1(f1 int); insert into t1 values(1),(2),(3); select f1, group_concat(f1+1) from t1 group by f1 with rollup; f1 group_concat(f1+1) 1 2 2 3 3 4 NULL 2,3,4 select count(distinct (f1+1)) from t1 group by f1 with rollup; count(distinct (f1+1)) 1 1 1 3 drop table t1; create table t1 (f1 int unsigned, f2 varchar(255)); insert into t1 values (1,repeat('a',255)),(2,repeat('b',255)); select f2,group_concat(f1) from t1 group by f2; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 f2 f2 253 255 255 Y 0 0 8 def group_concat(f1) 253 400 1 Y 128 0 63 f2 group_concat(f1) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2 drop table t1; set names latin1; create table t1 (a char, b char); insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b'); create table t2 select group_concat(b) as a from t1 where a = 'a'; create table t3 (select group_concat(a) as a from t1 where a = 'a') union (select group_concat(b) as a from t1 where a = 'b'); select charset(a) from t2; charset(a) latin1 select charset(a) from t3; charset(a) latin1 latin1 drop table t1, t2, t3; set names default; create table t1 (c1 varchar(10), c2 int); select charset(group_concat(c1 order by c2)) from t1; charset(group_concat(c1 order by c2)) latin1 drop table t1; CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a)); SET GROUP_CONCAT_MAX_LEN = 20000000; INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000)); INSERT INTO t1 SELECT a + 1, b FROM t1; SELECT a, CHAR_LENGTH(b) FROM t1; a CHAR_LENGTH(b) 1 120000 2 120000 SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1; CHAR_LENGTH( GROUP_CONCAT(b) ) 240001 SET GROUP_CONCAT_MAX_LEN = 1024; DROP TABLE t1; CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3); SELECT GROUP_CONCAT(a), x FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s GROUP BY x; GROUP_CONCAT(a) x 2 1,2 1 2,3 DROP TABLE t1; set names utf8; create table t1 ( x text character set utf8 not null, y integer not null ); insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0); set group_concat_max_len= 1022 + 10; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1032 1031 1027 aaaaaaa,÷÷÷÷ C3B7C3B7C3B7 set group_concat_max_len= 1022 + 9; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1031 1031 1027 aaaaaaa,÷÷÷÷ C3B7C3B7C3B7 set group_concat_max_len= 1022 + 8; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1030 1029 1026 aaaaaaaa,÷÷÷ C3B7C3B7C3B7 set group_concat_max_len= 1022 + 7; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1029 1029 1026 aaaaaaaa,÷÷÷ C3B7C3B7C3B7 set group_concat_max_len= 1022 + 6; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1028 1027 1025 aaaaaaaaa,÷÷ 612CC3B7C3B7 set group_concat_max_len= 1022 + 5; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1027 1027 1025 aaaaaaaaa,÷÷ 612CC3B7C3B7 set group_concat_max_len= 1022 + 4; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1026 1025 1024 aaaaaaaaaa,÷ 6161612CC3B7 set group_concat_max_len= 1022 + 3; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1025 1025 1024 aaaaaaaaaa,÷ 6161612CC3B7 set group_concat_max_len= 1022 + 2; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1024 1023 1023 aaaaaaaaaaa, 61616161612C set group_concat_max_len= 1022 + 1; select @x:=group_concat(x) from t1 group by y; select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12); @@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12) 1023 1023 1023 aaaaaaaaaaa, 61616161612C drop table t1; set group_concat_max_len=1024; set names latin1; create table t1 (f1 int unsigned, f2 varchar(255)); insert into t1 values (1,repeat('a',255)),(2,repeat('b',255)); select f2,group_concat(f1) from t1 group by f2; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 f2 f2 253 255 255 Y 0 0 8 def group_concat(f1) 252 1024 1 Y 128 0 63 f2 group_concat(f1) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2 drop table t1; CREATE TABLE t1(a TEXT, b CHAR(20)); INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3"); SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1; GROUP_CONCAT(DISTINCT UCASE(a)) ONE.1,TWO.2,ONE.3 SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1; GROUP_CONCAT(DISTINCT UCASE(b)) ONE.1,TWO.2,ONE.3 DROP TABLE t1; CREATE TABLE t1( a VARCHAR( 10 ), b INT ); INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), ( repeat( 'b', 10 ), 2); SET group_concat_max_len = 20; SELECT GROUP_CONCAT( a ) FROM t1; GROUP_CONCAT( a ) aaaaaaaaaa,bbbbbbbbb Warnings: Warning 1260 1 line(s) were cut by GROUP_CONCAT() SELECT GROUP_CONCAT( DISTINCT a ) FROM t1; GROUP_CONCAT( DISTINCT a ) aaaaaaaaaa,bbbbbbbbb Warnings: Warning 1260 1 line(s) were cut by GROUP_CONCAT() SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; GROUP_CONCAT( a ORDER BY b ) aaaaaaaaaa,bbbbbbbbb Warnings: Warning 1260 1 line(s) were cut by GROUP_CONCAT() SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1; GROUP_CONCAT( DISTINCT a ORDER BY b ) aaaaaaaaaa,bbbbbbbbb Warnings: Warning 1260 1 line(s) were cut by GROUP_CONCAT() SET group_concat_max_len = DEFAULT; DROP TABLE t1; SET group_concat_max_len= 65535; CREATE TABLE t1( a TEXT, b INTEGER ); INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 ); SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1; GROUP_CONCAT( a ORDER BY b ) a,b SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; GROUP_CONCAT(DISTINCT a ORDER BY b) a,b SELECT GROUP_CONCAT(DISTINCT a) FROM t1; GROUP_CONCAT(DISTINCT a) a,b SET group_concat_max_len= 10; SELECT GROUP_CONCAT(a ORDER BY b) FROM t1; GROUP_CONCAT(a ORDER BY b) a,b SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1; GROUP_CONCAT(DISTINCT a ORDER BY b) a,b SELECT GROUP_CONCAT(DISTINCT a) FROM t1; GROUP_CONCAT(DISTINCT a) a,b SET group_concat_max_len= 65535; CREATE TABLE t2( a TEXT ); INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) ); INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) ); SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2; LENGTH( GROUP_CONCAT( DISTINCT a ) ) 10001 CREATE TABLE t3( a TEXT, b INT ); INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 ); INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 ); INSERT INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 ); Warnings: Warning 1265 Data truncated for column 'a' at row 1 SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1; LENGTH( GROUP_CONCAT( a ) ) 65534 SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2; LENGTH( GROUP_CONCAT( a ) ) 65535 SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3; LENGTH( GROUP_CONCAT( a ) ) 65535 SET group_concat_max_len= DEFAULT; DROP TABLE t1, t2, t3; set names latin1; create table t1 (id int, name varchar(20)) DEFAULT CHARSET=utf8; insert into t1 (id, name) values (1, "�ra"); insert into t1 (id, name) values (2, "�ra"); select b.id, group_concat(b.name) from t1 a, t1 b group by b.id; id group_concat(b.name) 1 �ra,�ra 2 �ra,�ra drop table t1; create table t1(a bit not null); insert into t1 values (), (), (); Warnings: Warning 1364 Field 'a' doesn't have a default value select group_concat(distinct a) from t1; group_concat(distinct a) 0 select group_concat(distinct a order by a) from t1; group_concat(distinct a order by a) 0 drop table t1; create table t1(a bit(2) not null); insert into t1 values (1), (0), (0), (3), (1); select group_concat(distinct a) from t1; group_concat(distinct a) 1,0,3 select group_concat(distinct a order by a) from t1; group_concat(distinct a order by a) 0,1,3 select group_concat(distinct a order by a desc) from t1; group_concat(distinct a order by a desc) 3,1,0 drop table t1; create table t1(a bit(2), b varchar(10), c bit); insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1), (1, 'e', 1), (3, 'f', 1), (0, 'g', 1); select group_concat(distinct a, c) from t1; group_concat(distinct a, c) 10,01,00,31,11 select group_concat(distinct a, c order by a) from t1; group_concat(distinct a, c order by a) 00,01,11,10,31 select group_concat(distinct a, c) from t1; group_concat(distinct a, c) 10,01,00,31,11 select group_concat(distinct a, c order by a, c) from t1; group_concat(distinct a, c order by a, c) 00,01,10,11,31 select group_concat(distinct a, c order by a desc, c desc) from t1; group_concat(distinct a, c order by a desc, c desc) 31,11,10,01,00 drop table t1; End of 5.0 tests