GROUP_CONCAT with limit

Question

I have table with player-s in many-to-many relation with skill-s

The goal is to list the players and their "top 3 skills" with a single query.

create table player(
  id int primary key
);

create table skill(
  id int primary key,
  title varchar(100)
);

create table player_skills (
  id int primary key,
  player_id int,
  skill_id int,
  value int
);

Query:

SELECT 
p.id,  
group_concat(s.title  SEPARATOR ', ') as skills

FROM player p
LEFT JOIN player_skills ps ON ps.player_id = p.id
LEFT JOIN skill s ON s.id = ps.skill_id

WHERE ps.value > 2
-- skills limit 3 some how ...
group by p.id 
order by s.id


-- expected result
-- player_ID, skills
-- 1 , 'one'
-- 2 , 'one'
-- 3 , 'two, three, four'

As you can see in the fiddle the result of the query is missing only the limit of 3 skills.
I tried several variation of sub queries.. joins and so but with no effect.

may b you can get solution here http://stackoverflow.com/questions/1522509/how-to-hack-mysql-group-concat-to-fetch-a-limited-number-of-rows — Imran Qamer, Nov 20 '14 at 06:37

score 112 · Accepted Answer · edited May 23 '17 at 12:26

112

One somewhat hacky way to do it is to post-process the result of GROUP_CONCAT:

substring_index(group_concat(s.title SEPARATOR ','), ',', 3) as skills

Of course this assumes that your skill names don't contain commas and that their amount is reasonably small.

fiddle

A feature request for GROUP_CONCAT to support an explicit LIMIT clause is unfortunately still not resolved.

UPDATE: As user Strawberry points out, the table player_skills should have the tuple (player_id, skill_id) as its primary key, otherwise the schema allows for the same skill to be assigned to a player multiple times, in which case group_concat would not work as expected.

edited May 23 '17 at 12:26

Community

1
1

answered May 12 '14 at 12:05

Niklas B.

92,950
18
194
224

brilliant :) this is way better then several nested queries as the skills will be limited any way. – d.raev May 12 '14 at 12:09
2

Because of the strange (and probably redundant) PK, this should be GROUP_CONCAT(DISTINCT... – Strawberry May 12 '14 at 12:36
@Strawberry Thanks, I added a comment about this in my answer. Adding proper constraints is probably the way to go – Niklas B. May 12 '14 at 12:49
1

I expected more proper way but this hack is way cleaner and simple which makes it better for my case then several nested queries. Thanks for the out of the box thinking. – d.raev May 13 '14 at 06:26
Very nice. I would be concerned about the order of the results and would add an order by clause within the GROUP_CONCAT. – Jim Mc May 17 '16 at 17:04
In case the `GROUP_CONCAT` content is long, is MySQL/will MySQL be smart enough to avoid uselessly building the whole `GROUP_CONCAT` content (resulting in the need of increasing `group_concat_max_len`) before doing the `SUBSTRING_INDEX`? – Xenos Apr 04 '18 at 08:38
@Xenos Unfortunately not, as a quick test revealed. Long results will generate a warning `Row 270 was cut by GROUP_CONCAT()` if `group_concat_max_len` is not properly adjusted beforehand. – Martin Hennings Jun 25 '18 at 13:00
group_concate with order by as below :- substring_index(group_concat(s.title ORDER BY 1), ',', 3) as skills where 1 represents the s.title field itself. – Sujeet malvi Jan 16 '19 at 15:44

score 28 · Answer 2 · edited Aug 25 '16 at 15:45

28

Increase GROUP_CONCAT function length using GLOBAL group_concat_max_len GROUP_CONCAT() maximum length is 1024 characters.
What you can do is to set the GLOBAL group_concat_max_len in mysql

SET GLOBAL group_concat_max_len = 1000000;

Try this and it will work for sure.

edited Aug 25 '16 at 15:45

d.raev

9,216
8
58
79

answered Apr 13 '15 at 07:50

Zaib Khan

470
4
7

Romain Bruckert · Answer 3 · 2017-08-23T14:22:42.877

21

There is a much cleaner solution. Wrap it inside another SELECT statement.

SELECT GROUP_CONCAT(id) FROM (
    SELECT DISTINCT id FROM people LIMIT 4
) AS ids;

/* Result 134756,134754,134751,134750 */

edited Aug 23 '17 at 14:22

answered Jul 05 '17 at 12:57

Romain Bruckert

2,546
31
50

11

MySQL can't handle LIMITs inside of subqueries. It's been driving me mad for quite a while now :-/ – Theodore R. Smith May 22 '18 at 19:18
This worked for my using ezSQL, the mysqli version, thank you for the solution – carnini Apr 28 '23 at 20:36

score 14 · Answer 4 · answered Jan 19 '19 at 13:21

14

It is possible if you are using MariaDB 10.3.3+:

Support for LIMIT clause in GROUP_CONCAT() (MDEV-11297)

SELECT p.id,  
   GROUP_CONCAT(s.title ORDER BY title  SEPARATOR ', ' LIMIT 3) as skills
FROM player p
LEFT JOIN player_skills ps ON ps.player_id = p.id
LEFT JOIN skill s ON s.id = ps.skill_id
WHERE ps.value > 2
GROUP BY p.id 
ORDER BY s.id;

db<>fiddle demo

answered Jan 19 '19 at 13:21

Lukasz Szozda

162,964
23
234
275

SELECT GROUP_CONCAT(questionbankID ORDER BY RAND() SEPARATOR ', ' LIMIT 3 ) as questionbankID FROM questionbank WHERE clientID = 82 AND lessonID = 184 AND questionType = 'objective' AND type = 'A' Very similar Query is Giving me Error .. – Alok Jha May 24 '21 at 12:07

score 5 · Answer 5 · answered May 12 '14 at 12:51

Here's another solution. It includes an arbitrary mechanism for resolving ties, and employes a schema slightly differing from yours...

SELECT a.player_id
     , GROUP_CONCAT(s.title ORDER BY rank) skills
  FROM
     ( SELECT x.*, COUNT(*) rank
         FROM player_skills x
         JOIN player_skills y 
           ON y.player_id = x.player_id
          AND (y.value > x.value
           OR (y.value = x.value AND y.skill_id <= x.skill_id))
        GROUP 
           BY player_id, value, skill_id
       HAVING COUNT(*) <= 3
     ) a
  JOIN skill s
    ON s.skill_id = a.skill_id
 GROUP 
    BY player_id;

http://sqlfiddle.com/#!2/34497/18

Incidentally, if you have a presentation layer/application-level code, then consider doing all the GROUP_CONCAT stuff there. It's more flexible.

Majbah Habib · Answer 6 · 2020-06-05T11:25:34.753

4

You can follow the mentioned instructions to solve this kind of problems.

Instrcution1: Set the limit for group concate then write your query.

SET SESSION group_concat_max_len = 1200000;

Instruction 2: Then you can follow the given two example to find out your solution.

Example 1:

SELECT GROUP_CONCAT(app_id) AS ids FROM (
      SELECT DISTINCT app_id FROM email_queue 
) AS ids;

Example 2:

select GROUP_CONCAT(caption)  from email_queue group BY process_type_id;

Note 1: This is the table structure for the query of example1 and example2

.

Note 2: Here, 1200000 means that the query allow 1200000 characters maximum for group concate data.

edited Jun 05 '20 at 11:25

answered Apr 24 '19 at 05:57

Majbah Habib

8,058
3
36
38

Can you explain **why** one should set the limit? What's that needed for? The accepted answer does not contain this – Nico Haase Apr 24 '19 at 09:08
when the related column of GROUP_CONCAT contain more then 1024 characters then you need to use this for getting full result of GROUP_CONCAT function. For more details: https://www.namasteui.com/mysql-group_concat-maximum-length/ – Majbah Habib Jan 07 '20 at 11:56
Please add all such explanation to the answer itself, not to the comment section – Nico Haase Jan 07 '20 at 12:03

Gurwinder Singh · Answer 7 · 2017-05-13T08:30:24.623

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

select p.id,
    group_concat(s.title separator ', ') as skills
from player p
left join (
    select distinct ps.player_id,
        ps.skill_id,
        @rn := if(@player_id = player_id, @rn+1, if(@player_id := player_id, 1, 1)) as seqnum
    from player_skills ps
    cross join (select @rn := 0, @player_id := null) x
    where ps.value > 2
    order by player_id, value desc
    ) ps on p.id = ps.player_id and ps.seqnum <= 3
left join skill s on ps.skill_id = s.id
group by p.id;

Demo

This method doesn't require any table to read more than once.

GROUP_CONCAT with limit

7 Answers7

Demo

Linked

Related