## The Question :

*202 people think this question is useful*

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!

*The Question Comments :*

## The Answer 1

*390 people think this answer is useful*

GROUP_CONCAT has DISTINCT attribute:

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

## The Answer 2

*52 people think this answer is useful*

Using DISTINCT will work

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

REf:- this

## The Answer 3

*27 people think this answer is useful*

`DISTINCT`

: will gives you unique values.

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

## The Answer 4

*20 people think this answer is useful*

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.

## The Answer 5

*11 people think this answer is useful*

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*

## The Answer 6

*10 people think this answer is useful*

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