mysql GROUP_CONCAT duplicates
You need to use the DISTINCT option: GROUP_CONCAT(DISTINCT animal)
group-concat
Trouble with GROUP_CONCAT and Longtext in MySQL
According to the MySQL manual, the maximum length of GROUP_CONCAT is defined by the group_concat_max_len system variable, which defaults to 1024. This value can be increased, by using the following command: SET group_concat_max_len = <int> It should be noted, however, that the value of group_concat_max_len is itself limited by the value of another system variable, … Read more
is there a length limit to group_concat or another reason why it would not work on a text field
The group_concat result length is limited(truncated) to the value of the group_concat_max_len system variable. The default value of this variable is 1024. If you want change the value of the variable the syntax is: SET [GLOBAL | SESSION] group_concat_max_len = val; More info Mysql 5 docs
JPA Criteria API group_concat usage
I figured out how to do this with Hibernate-jpa-mysql: 1.) created a GroupConcatFunction class extending org.hibernate.dialect.function.SQLFunction (this is for single column group_concat for now) public class GroupConcatFunction implements SQLFunction { @Override public boolean hasArguments() { return true; } @Override public boolean hasParenthesesIfNoArguments() { return true; } @Override public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException … Read more
Join two tables with comma separated values
SELECT a.nid, GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName FROM Notes a INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0 GROUP BY a.nid SQLFiddle Demo
GROUP_CONCAT equivalent in Django
You can create your own Aggregate Function (doc) from django.db.models import Aggregate class Concat(Aggregate): function = ‘GROUP_CONCAT’ template=”%(function)s(%(distinct)s%(expressions)s)” def __init__(self, expression, distinct=False, **extra): super(Concat, self).__init__( expression, distinct=”DISTINCT ” if distinct else ”, output_field=CharField(), **extra) and use it simply as: query_set = Fruits.objects.values(‘type’).annotate(count=Count(‘type’), name = Concat(‘name’)).order_by(‘-count’) I am using django 1.8 and mysql 4.0.3
How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
I’ve worked around this using SUBSTRING_INDEX. For example: SELECT SUBSTRING_INDEX(GROUP_CONCAT(Field1 SEPARATOR ‘,’), ‘,’, [# of elements to return]) FROM Table1;
MySQL: GROUP_CONCAT with LEFT JOIN
Use: SELECT t.*, x.combinedsolutions FROM TICKETS t LEFT JOIN (SELECT s.ticket_id, GROUP_CONCAT(s.soution) AS combinedsolutions FROM SOLUTIONS s GROUP BY s.ticket_id) x ON x.ticket_id = t.ticket_id Alternate: SELECT t.*, (SELECT GROUP_CONCAT(s.soution) FROM SOLUTIONS s WHERE s.ticket_id = t.ticket_id) AS combinedsolutions FROM TICKETS t
GROUP_CONCAT in SQLite
You need to add GROUP BY clause when you are using aggregate function. Also use JOIN to join tables. So try this: SELECT AI._id, GROUP_CONCAT(Name) AS GroupedName FROM ABSTRACTS_ITEM AI JOIN AUTHORS_ABSTRACT AAB ON AI.ID = AAB.ABSTRACTSITEM_ID JOIN ABSTRACT_AUTHOR AAU ON AAU._id = AAB.ABSTRACTAUTHOR_ID GROUP BY tbl._id; See this sample SQLFiddle What you were trying … Read more
How create json format with group-concat mysql?
With the newer versions of MySQL, you can use JSON_OBJECT function to achieve the desired result, like so: GROUP_CONCAT( JSON_OBJECT( ‘name’, name, ‘phone’, phone ) ) AS list To get the SQL response ready to be parsed as an array: CONCAT( ‘[‘, GROUP_CONCAT( JSON_OBJECT( ‘name’, name, ‘phone’, phone ) ), ‘]’ ) AS list This … Read more