My mistakes

MySQL GROUP_CONCAT headache

Posted on: September 27, 2011

create table test_table(id integer unsigned,cName varchar(50));

insert into test_table values(1,’test1′);
insert into test_table values(2,’test2′);
insert into test_table values(3,’test3′);

select group_concat(cName separator ‘,’)
from test_table

+———————————-+
| group_concat(cName separator ‘,’) |
+———————————-+
| test1,test2,test3 |
+———————————-+

But now,I want to limit the group_concat to the first two rows of the result:

+———————————-+
| group_concat(name separator ‘,’) |
+———————————-+
| test1,test2 |
+———————————-+

Solution :
We cannot use the limit with the group_concat so the better solution is to go for SUBSTRING_INDEX like

SELECT
SUBSTRING_INDEX(group_CONCAT(cName) , ‘,’, 2) as cName from test_table

Advertisements
Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: