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

Filtering Duplicates in GROUP_CONCAT() Results Using MySQL's DISTINCT

In database queries, the GROUP_CONCAT() function is frequently used to concatenate values from multiple rows into a single string. However, sometimes, the resulting string may contain duplicate values. To address this issue, MySQL provides the DISTINCT attribute within GROUP_CONCAT().

GROUP_CONCAT() with DISTINCT: A Practical Example

Consider the following table with a "categories" column:

categories

test1 test2 test3

test4

test1 test3

test1 test3

If we execute the following query:

SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table;

We will get the following result:

test1 test2 test3 test4 test1 test3

However, we may want to exclude duplicate values, resulting in:

test1 test2 test3 test4

Using DISTINCT to Eliminate Duplicates

To achieve this, we simply add the DISTINCT attribute to the GROUP_CONCAT() function:

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

By specifying DISTINCT, MySQL will eliminate duplicate values from the concatenated string, resulting in the desired output. Note that the ORDER BY clause ensures that the concatenated values are sorted in ascending order.

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