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!

Journalize

Answer 1

GROUP_CONCAT has DISTINCT attribute:

SELECT GROUP_CONCAT(DISTINCT categories ORDER BY categories ASC SEPARATOR ' ') FROM table

Senile

I'm not sure why this answer has so many upvotes, 'cause it doesn't solve the question as it was asked. Maybe the question is poorly written itself? – Friday

If so many people found it useful it must be right, maybe you have a different problem than this question. – Senile

using mysql for 13 years I was not aware of this, thanks :D – Nunci

Answer 2

Using DISTINCT will work

SELECT GROUP_CONCAT(DISTINCT(categories) SEPARATOR ' ') FROM table

REf:- this

Diurnal

Answer 3

DISTINCT: will gives you unique values.

SELECT GROUP_CONCAT(DISTINCT(categories )) AS categories FROM table

Knowledge

Answer 4

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.

Subclavius

It appears your interpretation of OP's question may be right; however, I think it should be pointed out that normalizing the data by creating a "blah_to_categories" and a "categories" table for the appropriate many-to-many relationship would be the best practice here, and would add a lot of flexibility. Still, your answer is a smart workaround for anyone who inherits such a denormalized schema. It also could probably be adapted for the purpose of generating a migration from the old to the normalized schema. – Cruise

Answer 5

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 ' ')

Nessa

Answer 6

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

This will return distinct values like: test1,test2,test4,test3

Philanthropic

Answer 7

If you DBMS doesn't support SEPARATOR, you can write:

SELECT GROUP_CONCAT(DISTINCT(categories) || '\n') FROM table

Watcher