Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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.
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.
–
–
–
–
–
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.
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 */
–
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
–
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
( 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
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.
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.
–
–
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;
This method doesn't require any table to read more than once.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
