[ACCEPTED]-How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?-group-concat

Accepted answer
Score: 21

I've worked around this using SUBSTRING_INDEX.

For example:

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

0

Score: 5

An example for Charles answer:

basic:

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

extended:

CAST 2 can be useful if result are truncated by 1 buffer:

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

Not really an answer to your question but 4 a ref for other people who also would want 3 to use a LIMIT clause in GROUP_CONCAT():

A 2 feature-request was filed long ago to MySql developers. Not 1 yet implemented :-(

Score: 2

Use a temporary table / subquery to limit 6 results? Without seeing your query, it'll 5 be hard to give solid advice for that route.

However, you 4 may find the group_concat_max_len setting to be more useful. It 3 controls the maximum length of a GROUP_CONCAT operation, in 2 string length. Raise it to prevent broken 1 GROUP_CONCATs, when you can't afford to limit results.

Score: 2

You can simulate the partitioned row_number 5 using user variables and then limit rows 4 and apply group_concat:

Consider the following 3 table:

create table your_table (
    id int primary key autoincrement,
    category int,
    value int
);

and data:

insert into your_table (category, value) 
values
(1,  1), (1,  2), (1,  3), (1,  4), (1,  5),
(2,  6), (2,  7), (2,  8), (2,  9), (2, 10),
(3, 11), (3, 12), (3, 13), (3, 14), (3, 15);

And we want is top 3 (in 2 order of latest id) value per category concatenated:

select category, 
    group_concat(value order by id desc) as value_con
from (
    select t.*,
        @rn := if(@category = category, @rn + 1, if(@category := category,1, 1)) as seqnum
    from your_table t
    cross join (select @category := null, @rn := 0) x
    order by t.category, t.id desc
    ) t
where seqnum <= 3
group by category;

Output:

category    value_con
1           5,4,3
2           10,9,8
3           15,14,13

Here 1 is a demo of this.

Score: 0

For those cases where you cannot use a temp 15 table, The best way I know of is to select 14 an obscure separator and then truncate starting 13 at the first instance of said character. This 12 example uses the NUL character.

select substring_index(group_concat(field separator '\0'), '\0', 5) from table;

Where field is 11 the name of the field, 5 is the number of 10 results.

The drawback is if your first row 9 contains that character, it will be a partial 8 result.

A workaround would be to replace 7 '\0' with a longer random string.

It's good to 6 know that field could be replaced to include 5 more information using concat.

Keep in mind the 4 group_concat_max_len defaults to 1024 characters, so you should 3 look into changing that either globally 2 or within your application if you want more 1 than that.

More Related questions