MySQL DISTINCT on a GROUP CONCAT

Question

I am doing SELECT GROUP CONCAT categories SEPARATOR      FROM table  Sample data below   categories ---------- test1 test2 test3 test4 test1 test3 test1 test3   However  I am getting test1 test2 test3 test4 test1 test3 back and I would like to get test1 test2 test3 test4 back  Any ideas   Many thanks

User · Answer

SELECT   GROUP CONCAT DISTINCT  category   FROM     SELECT     SUBSTRING INDEX SUBSTRING INDEX tableName categories       numbers n        -1  category   FROM     numbers INNER JOIN tableName     ON LENGTH tableName categories  gt  LENGTH REPLACE tableName categories            numbers n-1     s       This will return distinct values like  test1 test2 test4 test3

User · Answer

Using DISTINCT will work   SELECT GROUP CONCAT DISTINCT categories  SEPARATOR      FROM table   REf - this

User · Answer

GROUP CONCAT has DISTINCT attribute   SELECT GROUP CONCAT DISTINCT categories ORDER BY categories ASC SEPARATOR      FROM table

User · Answer

DISTINCT  will gives you unique values   SELECT GROUP CONCAT DISTINCT categories    AS categories FROM table

User · Answer

Other answers to this question do not return what the OP needs  they will return a string like   test1 test2 test3 test1 test3 test4    notice that test1 and test3 are duplicated  while the OP wants to return this string   test1 test2 test3 test4   the problem here is that the string  test1 test3  is duplicated and is inserted only once  but all of the others are distinct to each other   test1 test2 test3  is distinct than  test1 test3   even if some tests contained in the whole string are duplicated    What we need to do here is to split each string into different rows  and we first need to create a numbers table   CREATE TABLE numbers  n INT   INSERT INTO numbers VALUES  1   2   3   4   5   6   7   8   9   10     then we can run this query   SELECT   SUBSTRING INDEX      SUBSTRING INDEX tableName categories       numbers n                -1  category FROM   numbers INNER JOIN tableName   ON     LENGTH tableName categories  gt       LENGTH REPLACE tableName categories            numbers n-1    and we get a result like this   test1 test4 test1 test1 test2 test3 test3 test3   and then we can apply GROUP CONCAT aggregate function  using DISTINCT clause   SELECT   GROUP CONCAT DISTINCT category ORDER BY category SEPARATOR      FROM     SELECT     SUBSTRING INDEX SUBSTRING INDEX tableName categories       numbers n        -1  category   FROM     numbers INNER JOIN tableName     ON LENGTH tableName categories  gt  LENGTH REPLACE tableName categories            numbers n-1     s    Please see fiddle here

User · Answer

You can simply add DISTINCT in front   SELECT GROUP CONCAT DISTINCT categories SEPARATOR        if you want to sort   SELECT GROUP CONCAT DISTINCT categories ORDER BY categories ASC SEPARATOR

[mysql] MySQL DISTINCT on a GROUP_CONCAT()

Examples related to mysql

Examples related to group-concat