How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?

Question

I somehow need this feature,but MySQL doesn't support it at this moment.

I'm using GROUP_CONCAT(CONCAT(...)) to generate a xml-like stuff.

But when the size exceeds the limit,the xml is just broken!

So I have to somehow make it only retrieve 5 rows !

Answer 1

I've worked around this using SUBSTRING_INDEX.

For example:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(Field1 SEPARATOR ','), ',', [# of elements to return])
FROM Table1;

Answer 2

An example for Charles answer:

basic:

SELECT GROUP_CONCAT( field ) FROM ( SELECT field FROM table LIMIT 200 )

extended:

CAST can be useful if result are truncated by buffer:

SELECT CAST( GROUP_CONCAT( field ) AS CHAR(2048) ) FROM ( SELECT field FROM table LIMIT 200 )