CODEWITHSUNDEEP
mysqlgroup-by
Bryan
Bryan

Reputation: 1241

Groupings in MySQL

I have a tables that contains the below values.

region      state
---------   ----------
region01    state01
region01    state02
region02    state03

On the above data, I want to get all the regions in just single row that's why I've to used the GROUP_CONCAT. But it seems that my below query duplicates the region01 and having a result of

region01;region01;region02

Can any one help me on how to improve the below query so that the return value should not duplicates the same data

SELECT GROUP_CONCAT(region SEPARATOR ';') AS region
  FROM table1
 WHERE field1='BUSINESS01';

Upvotes: 0

Views: 36

Answers (2)

Sonal Khunt
Sonal Khunt

Reputation: 1894

u have to use this syntax

SELECT GROUP_CONCAT(DISTINCT region SEPARATOR ';') AS region
  FROM table1
 WHERE field1='BUSINESS01';

its give u only unique field values

Upvotes: 1

Bjoern
Bjoern

Reputation: 16314

Add a DISTINCT.

SELECT GROUP_CONCAT(DISTINCT region SEPARATOR ';') AS region
FROM table1
WHERE field1='BUSINESS01';

From the MySQL manual:

DISTINCT specifies removal of duplicate rows from the result set.

Upvotes: 2

Related Questions