d.raev d.raev - 3 months ago 12
MySQL Question

GROUP_CONCAT with limit

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.

fiddle

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.

Answer

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.