CODEWITHSUNDEEP
mysqlsqlgroup-bygroup-concat
Berry Blue
Berry Blue

Reputation: 16482

MySql query using GROUP_CONCAT and DISTINCT

Here is an example query I am doing. It produces 1,A,1,A,3,B,2,B,4,C,4,C.

SELECT GROUP_CONCAT(CONCAT(ID, ','), Value ORDER BY Value) FROM TableName

Here's a SQL Fiddle Demo.

I want to call DISTINCT on the column Value so that it will output 1,A,2,B,4,C instead.

Is this possible? Everything I try gives me an error.

Upvotes: 2

Views: 282

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

To get the distinct combination Id, Value, you will need to GROUP BY value and select the MIN(ID) inside a subquery, then select GROUP_CONCAT in the outer query the same way you did.

Something like this:

SELECT GROUP_CONCAT(CONCAT(ID, ','), Value ORDER BY Value)
FROM
(
  SELECT value, MIN(ID) AS ID
  FROM tablename
  GROUP BY value
) AS t;

Updated SQL Fiddle Demo

This will give you:

|      RESULT |
---------------
| 1,A,2,B,4,C |

Upvotes: 1

Related Questions