Geek asks and answers

Has anyone discovered a (simple) way to limit group_concat strings?

I have a fairly large request to get information about reports from each country, and right now, the only way to limit this is to put Limit 10or some number at the end, which will limit countries. However, I want to limit group_concatto 10 results in each country, which in my case will somehow limit to 10 each instance of the word group_concat.

My current request:

SELECT country,
GROUP_CONCAT(docID),
GROUP_CONCAT(analyst),
GROUP_CONCAT(region),
GROUP_CONCAT(report),
GROUP_CONCAT(topic),
MAX((date)) AS date,
MAX((docID)) AS docID,
GROUP_CONCAT(date) AS dates,
GROUP_CONCAT(event) AS events,
GROUP_CONCAT(province) AS provinces
FROM reports GROUP BY country 
ORDER BY date DESC, docID DESC

I saw this question, and I did not see any really good answers. I know that the function is not built in MySQL, since you can only restrict characters. Has anyone solved this problem before?

+3
source share
2 answers


, #. , group_concat .
, 20 .

:

SET group_concat_max_len = 10*20+9; /*execute this first.*/
/*10 items, 20 bytes each + 9 bytes for the separator*/

SELECT country,
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),docID),20)),'#','') AS docIDs, 
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),analyst),20)),'#','') AS analysts, 
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),region,20)),'#','') AS regions, 
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),report,20)),'#','') AS reports, 
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),topic,20)),'#','') AS topics, 
MAX((date)) AS `date`,  /* LATEST DATE*/
MAX((docID)) AS docID,  /* LATEST DOC*/
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),date,20)),'#','') AS dates, 
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),event,20)),'#','') AS events, 
REPLACE(GROUP_CONCAT(RIGHT(CONCAT(repeat('#',20),province,20)),'#','') AS provinces 
FROM reports 
GROUP BY country ORDER BY `date` DESC, docID DESC

:

x= CONCAT(repeat('#',20),docID)  adds 20 x #################### in front of docID
y= RIGHT(X,20)                   Takes the rightmost 20 chars of that
z= GROUP_CONCAT(y)               strings these together up to max_len
result = REPLACE(z,'#','') removes the `#` so the result looks normal.

group_concat
group_concat UDF.
.

: http://www.codeproject.com/KB/database/mygroupconcat.aspx?display=Mobile

+2

select user_id,SUBSTRING_INDEX(group_concat(posts.id order by rand()),',',3) from posts inner join users on users.id = posts.user_id group by posts.user_id;
+2

More articles:


All Articles