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 !

may be you can get your answer here http://stackoverflow.com/questions/3378324/limit-ignored-in-query-with-group-concat http://stackoverflow.com/questions/23608464/group-concat-with-limit — Imran Qamer, Nov 20 '14 at 06:39

score 21 · Answer 1 · edited Jun 11 '12 at 21:37

21

I've worked around this using SUBSTRING_INDEX.

For example:

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

edited Jun 11 '12 at 21:37

Peter Lang

54,264
27
148
161

answered Jun 11 '12 at 20:15

Denis Dupere

211
2
2

score 5 · Answer 2 · edited Jan 05 '12 at 11:58

5

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 )

edited Jan 05 '12 at 11:58

Marek Grzenkowicz

17,024
9
81
111

answered Jan 05 '12 at 11:43

rekans

342
3
11

Yes, it will work. But it will need a more complex constuction for a query with `GROUP BY`. – ypercubeᵀᴹ Jan 05 '12 at 12:51
1

I got an error for "Every derived table must have its own alias" when using the first query. To fix that, I changed it to " SELECT GROUP_CONCAT( field ) FROM ( SELECT field FROM table LIMIT 200 ) AS alias " Thanks for the answer. – Henry Apr 01 '13 at 01:52

score 4 · Answer 3 · answered May 05 '10 at 12:23

4

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

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

answered May 05 '10 at 12:23

Serge Wautier

21,494
13
69
110

Charles · Answer 4 · 2012-01-05T18:44:36.997

2

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

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

edited Jan 05 '12 at 18:44

answered Oct 06 '09 at 06:38

Charles

50,943
13
104
142

Gurwinder Singh · Answer 5 · 2017-05-13T16:01:37.353

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

Consider the following 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 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 is a demo of this.

wow! so it is possible to put the where clause outside the subquery! this inadvertently solves my problem where i had nested subqueries and MySQL doesn't pass column references deeper than one level. so when i moved the where clause outside the subquery then column reference was on the first level and the query ran! hmmm i guess if we google long enough we could find answers to anything! LOL — some_groceries, May 31 '21 at 17:51

score 0 · Answer 6 · answered Aug 28 '14 at 19:05

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

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

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

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

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

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

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

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

6 Answers6

Linked