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

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)>=
    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)>=LENGTH(REPLACE(tableName.categories, ' ', ''))+numbers.n-1
  ) s;

Please see fiddle here.

User · Answer

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

User · Answer

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

User · Answer

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

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

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

[mysql] MySQL DISTINCT on a GROUP_CONCAT()

Examples related to mysql

Examples related to group-concat