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 stackoverflow.com/questions/1522509/… — Imran Qamer
– Imran Qamer, Commented Nov 20, 2014 at 6:37

Community · Accepted Answer · 2017-05-23 12:26:09Z

113

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, 2017 at 12:26

CommunityBot

11 silver badge

answered May 12, 2014 at 12:05

Niklas B.

95.7k18 gold badges201 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

d.raev Over a year ago

brilliant :) this is way better then several nested queries as the skills will be limited any way.

Strawberry Over a year ago

Because of the strange (and probably redundant) PK, this should be GROUP_CONCAT(DISTINCT...

Niklas B. Over a year ago

@Strawberry Thanks, I added a comment about this in my answer. Adding proper constraints is probably the way to go

d.raev Over a year ago

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.

Jim Mc Over a year ago

Very nice. I would be concerned about the order of the results and would add an order by clause within the GROUP_CONCAT.

|

d.raev · Accepted Answer · 2016-08-25 15:45:05Z

29

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, 2016 at 15:45

d.raev

9,5848 gold badges63 silver badges84 bronze badges

answered Apr 13, 2015 at 7:50

Zaib Khan

4804 silver badges8 bronze badges

Comments

Romain Bruckert · Accepted Answer · 2017-08-23 14:22:42Z

22

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, 2017 at 14:22

answered Jul 5, 2017 at 12:57

Romain Bruckert

2,62032 silver badges50 bronze badges

2 Comments

Theodore R. Smith Over a year ago

MySQL can't handle LIMITs inside of subqueries. It's been driving me mad for quite a while now :-/

carnini Over a year ago

This worked for my using ezSQL, the mysqli version, thank you for the solution

Lukasz Szozda · Accepted Answer · 2019-01-19 13:21:29Z

19

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, 2019 at 13:21

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

1 Comment

Alok Jha Over a year ago

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 ..

Strawberry · Accepted Answer · 2014-05-12 12:51:10Z

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 · Accepted Answer · 2020-06-05 11:25:34Z

5

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 5, 2020 at 11:25

answered Apr 24, 2019 at 5:57

Majbah Habib

8,6064 gold badges41 silver badges42 bronze badges

3 Comments

Nico Haase Over a year ago

Can you explain why one should set the limit? What's that needed for? The accepted answer does not contain this

Majbah Habib Over a year ago

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: namasteui.com/mysql-group_concat-maximum-length

Nico Haase Over a year ago

Please add all such explanation to the answer itself, not to the comment section

Gurwinder Singh · Accepted Answer · 2017-05-13 08:30:24Z

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.

Collectives™ on Stack Overflow

GROUP_CONCAT with limit

7 Answers 7

8 Comments

Comments

2 Comments

1 Comment

Comments

3 Comments

Demo

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

8 Comments

Comments

2 Comments

1 Comment

Comments

3 Comments

Comments

Linked

Related