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 seperator ',') 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(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(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 seperator ',') 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 seperator ',') 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(a)) 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 'group statement' 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 tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; a_id b_list 1 1,2,3 2 4,5 3 NULL DROP TABLE t2; 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;