How to avoid duplication in GROUP_CONCAT?

Question

I have query which concatenate strings if they belong to one group.

    SELECT e.id, 
        ke.value,
        re.value AS re_value,
        GROUP_CONCAT(g.value,', ')
        FROM entry e
                INNER JOIN k_ele ke ON e.id = ke.fk
                INNER JOIN r_ele re ON e.id = re.fk
                INNER JOIN sense s ON e.id = s.fk
                INNER JOIN gloss g ON s.id = g.fk
WHERE g.lang IS NULL
GROUP BY s.id
ORDER BY re_value

But

GROUP_CONCAT(g.value,', ')

is giving this result.

affectionate relationship, affectionate relationship, affectionate relationship, affectionate relationship, loving relationship, loving relationship, loving relationship, loving relationship

As you can see there are duplications in concatenation. How to avoid duplications in concatenations?

score 38 · Accepted Answer · answered Aug 17 '13 at 12:32

38

GROUP_CONCAT(DISTINCT g.value)

answered Aug 17 '13 at 12:32

Joe Rakhimov

3,734
7
36
91

7

This works, but you can not specify a delimiter when using `GROUP_CONCAT(DISTINCT` in SQLite, if you wanted to change the delimiter from the default `,` you would either have to use the subquery approach as displayed in CL's answer, or use a replace (I would imagine the subquery approach is much better). – GarethD Aug 30 '13 at 07:22
6

I had to jam this into an already nasty query, so I went with ```replace(GROUP_CONCAT(DISTINCT g.value), ',', the_delimiter)```. It's terrible, but I wanted to show a quick way to deal with this if you're stuck with your other delimiter and a very large query that would make the subquery difficult. You must be positive that your values don't have commas in them for this to work. – Kelly Sep 10 '14 at 19:22
For large data sub query is the right way to do. In this case Distinct will be just a hack. – Muhammad Babar Feb 10 '15 at 18:54

score 6 · Answer 2 · answered Aug 17 '13 at 10:05

6

You have to remove the duplicates before applying the GROUP_CONCAT, which typically requires a subquery:

SELECT a, GROUP_CONCAT(b)
FROM (SELECT DISTINCT a, b
      FROM MyTable)
GROUP BY a

answered Aug 17 '13 at 10:05

CL.

158,085
15
181
214

How to avoid duplication in GROUP_CONCAT?

2 Answers2