GROUP_CONCAT with limit
group-concat, mysql
Solution
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.
Problem
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.