PHP MySQL GROUP_CONCAT Limit

Question

I couldn't find wether this was possible so, is there any option to limit a GROUP_CONCAT in MySQL-function?

E.g.:

GROUP_CONCAT(ColName ORDER BY ColName DESC LIMIT 5)

I don't want to use a subquery since this will seriously slow down the performance. I can slice the array later in PHP, but I was wondering or MySQL had an option to achieve this in MySQL already.

*"I don't want to use a subquery"* - You have to do something slightly bonkers to apply `LIMIT` to a sub-query anyway : http://stackoverflow.com/questions/7124418/mysql-subquery-limit — CD001, Apr 06 '17 at 11:43

Gordon Linoff · Accepted Answer · 2017-04-06T11:39:41.810

14

No, but you can do this:

SUBSTRING_INDEX(GROUP_CONCAT(ColName ORDER BY ColName DESC), ',', 5)

You may want to pay attention to the group concat maximum length (see group_concat_max_len), if the intermediate string might be larger than 1024 characters. You can change the default.

edited Apr 06 '17 at 11:39

answered Apr 06 '17 at 11:37

Gordon Linoff

1,242,037
58
646
786

1

you have missed one ')' after desc – Rams Apr 06 '17 at 11:39
@GordonLinoff, great solution, thanks. The 1024 will be large enough, since I will only return 5 integers. – TVA van Hesteren Apr 06 '17 at 11:53

PHP MySQL GROUP_CONCAT Limit

1 Answers1