How Can I Remove Duplicates from MySQL's GROUP_CONCAT() Function?

Eliminate Duplicates from GROUP_CONCAT() using DISTINCT

In MySQL, GROUP_CONCAT() is a useful function for aggregating values from multiple rows into a single comma-separated string. However, when working with non-unique values, duplicates can appear in the resulting string. To address this, the DISTINCT attribute can be used within the GROUP_CONCAT() function.

Problem:

Consider the following sample data table with a "categories" column:

categories

test1 test2 test3

test4

test1 test3

test1 test3

When performing the following GROUP_CONCAT() query:

SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table

The result is:

test1 test2 test3 test4 test1 test3

However, the desired result is:

test1 test2 test3 test4

Solution:

To eliminate duplicate values from the GROUP_CONCAT() result, use the DISTINCT attribute as follows:

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

The DISTINCT attribute ensures that only unique values are included in the concatenated string. The ORDER BY clause is used to sort the values in ascending order. The result is a comma-separated string containing only the distinct categories:

test1 test2 test3 test4

The above is the detailed content of How Can I Remove Duplicates from MySQL's GROUP_CONCAT() Function?. For more information, please follow other related articles on the PHP Chinese website!