Using Limit with group_contact in MySQL 5

Question

Good morning guys, I have a question:

I have the following sample SQL:

group_concat( DISTINCT `mvu5877_anuncios_photos`.`image` ORDER BY `mvu5877_anuncios_photos`.`order` ASC SEPARATOR ',' ) AS `images`

In SEPARATOR I need to define a quantity instead of bringing all the items. In MariaDB I can do this just past the Limit at the end. Example:

group_concat( DISTINCT `mvu5877_anuncios_photos`.`image` ORDER BY `mvu5877_anuncios_photos`.`order` ASC SEPARATOR ',' LIMIT 4 ) AS `images

More in MySQL 5.7.32 the syntax error. Any suggestion?

Answer 1

You can use SUBSTRING_INDEX() so that you get the top 4 values in the results:

SUBSTRING_INDEX(
  GROUP_CONCAT(DISTINCT `mvu5877_anuncios_photos`.`image` ORDER BY `mvu5877_anuncios_photos`.`order`),
  ',',
  4
)