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 : stackoverflow.com/questions/7124418/mysql-subquery-limit — CD001
– CD001, Commented Apr 6, 2017 at 11:43

Gordon Linoff · Accepted Answer · 2017-04-06 11:39:41Z

15

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 6, 2017 at 11:39

answered Apr 6, 2017 at 11:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rams Over a year ago

you have missed one ')' after desc

TVA van Hesteren Over a year ago

@GordonLinoff, great solution, thanks. The 1024 will be large enough, since I will only return 5 integers.

Collectives™ on Stack Overflow

PHP MySQL GROUP_CONCAT Limit

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related