CODEWITHSUNDEEP
mysqlgroup-concat
Ultrazz008
Ultrazz008

Reputation: 1688

MySQL GROUP_CONCAT doesn't return all results

i've used GROUP_CONCAT to get results splitted by comma (,), but when i saw, the GRUP_CONCAT returned only 205 splitted numbers, but in the database there's 2448 results (different aid). Here is my query:

SELECT GROUP_CONCAT(`aid`) As favoriti 
FROM `z_web_favoriti` 
WHERE `kup_id`='1' AND `pos_id`='571'

When i execute:

SELECT DISTINCT `aid` 
FROM `z_web_favoriti` 
WHERE `kup_id`='1' AND `pos_id`='571'

I get the following result: Showing rows 0 - 29 (2448 total,..)

Anyone has some solution why it isn't working? I've searched on stackoverflow for similar problem, but i couldn't find it..

Upvotes: 6

Views: 5155

Answers (4)

kush
kush

Reputation: 645

SET SESSION group_concat_max_len = 1000000; -- Example: Set a larger limit

Upvotes: 0

McNets
McNets

Reputation: 10807

Probably you have exceeded GROUP_CONCAT maximum length.

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Upvotes: 11

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

Please try below code.

SET GLOBAL group_concat_max_len=15000;
SELECT GROUP_CONCAT(`aid`) As favoriti 
FROM `z_web_favoriti` 
WHERE `kup_id`='1' AND `pos_id`='571'

Hope this will helps.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

By default, the maximum length for group_concat() is 1,024.

You can change this to a larger value by changing the value of the system variable group_concat_max_len.

The documentation explains this.

Upvotes: 1

Related Questions