CODEWITHSUNDEEP
sqlmysqlgroup-concat
Misier
Misier

Reputation: 1533

MySQL GROUP_CONCAT headache

For performance,I need to set a limit for the GROUP_CONCAT,

and I need to know if there are rows not included.

How to do it?

EDIT

Let me provide a contrived example:

create table t(qid integer unsigned,name varchar(30));

insert into t value(1,'test1');    
insert into t value(1,'test2');    
insert into t value(1,'test3');

select group_concat(name separator ',') 
  from t 
 where qid=1;

+----------------------------------+
| group_concat(name separator ',') |
+----------------------------------+
| test1,test2,test3                |
+----------------------------------+

But now,I want to group 2 entries at most,and need to know if there is some entry not included in the result:

+----------------------------------+
| group_concat(name separator ',') |
+----------------------------------+
| test1,test2                      |
+----------------------------------+

And I need to know that there is another entry left(in this case it's "test3")

Upvotes: 1

Views: 1935

Answers (2)

Sabeen Malik
Sabeen Malik

Reputation: 10880

this should do the trick

SELECT 
SUBSTRING_INDEX(group_CONCAT(name) , ',', 2) as list , 
( if(count(*) > 2 , 1 , 0)) as more
 FROM 
   t 
 WHERE 
   qid=1

Upvotes: 1

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

How are you going to set the limit? And what performance issues will it solve?

You can get the number of rows in a group using count(*) and compare it to the limit.

Upvotes: 0

Related Questions