Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 210
d.raev Avatar asked Oct 09 '22 06:10

d.raev


People also ask

Is there a length limit to Group_concat?

Show activity on this post. I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

How to increase GROUP_ CONCAT function length in MySQL?

SET SESSION group_concat_max_len = 1000000; SELECT group_concat(`field`) FROM TABLE_NAME GROUP BY `field`; You can also do this in sharing hosting, but when you use an other session then you need to repeat the SET SESSION command.

What is the use of Group_concat?

GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.

What is Group_concat in MySQL?

The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.


Video Answer


3 Answers

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.

like image 80
Niklas B. Avatar answered Oct 20 '22 00:10

Niklas B.


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.

like image 20
Zaib Khan Avatar answered Oct 20 '22 01:10

Zaib Khan


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 */
like image 34
Romain Bruckert Avatar answered Oct 19 '22 23:10

Romain Bruckert