GROUP_CONCAT with limit
MysqlGroup ConcatMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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.
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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 */
Solution 4 - Mysql
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;
Solution 5 - Mysql
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.
Solution 6 - Mysql
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.
Solution 7 - Mysql
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.