MySQL Group_Concat Distinct By Id

Question

Is it possible to group_concat records by distinct Ids:

GROUP_CONCAT(Column2 BY DISTINCT Column1)

I need to get the values from column2 by distinct values from column1. Because there are repeating values in column 2, that's why I can't use distinct on column2.

Any thoughts on this? Thanks!

EDIT 1

Sample Table Records:

ID  Value
1   A
1   A
2   B
3   B
4   C
4   C

Using the GROUP_CONCAT the I wanted [GROUP_CONCAT(Value BY DISTINCT Id)], I will have an output:

A, B, B, C

EDIT 2

Somehow got my group_concat working:

GROUP_CONCAT(DISTINCT CONCAT(Id, '|', Value))

This will display the concatenated values by distinct id, just need to get rid of the Id somewhere. You can do it without the concat function, but I need the separator. This may not be a good answer but I'll post it anyway.

can you sample records with desired result? – John Woo Sep 05 '12 at 02:52 — John Woo, Sep 05 '12 at 02:52

score 6 · Accepted Answer · answered Sep 05 '12 at 02:59

6

Try this one, (the simpliest way)

SELECT GROUP_CONCAT(VALUE)
FROM
(
    SELECT DISTINCT ID, VALUE
    FROM TableName
) a

SQLFiddle Demo

answered Sep 05 '12 at 02:59

John Woo

258,903
69
498
492

Thanks John! So there is no functionality for `GROUP_CONCAT` on my question? I have a very long query, I can't use distinct inside the subquery. – KaeL Sep 05 '12 at 03:05
1

@KaeL not sure to answer your question but i really think that you need to create a separate query to get distinct rows den join it with your current. – John Woo Sep 05 '12 at 03:13
I see, thanks a lot. I'll try to use a separate query, I'm worrying a little with the performance, got very large tables involved. – KaeL Sep 05 '12 at 03:20
if you have proper index on your tables then you don't have to worry much. don't worry if you have subqueries. *"it has to be done to achieve your results."* – John Woo Sep 05 '12 at 03:22
Thanks for the info John, my tables are well indexed. I just added some tweaks to make my `group_concat` work. I'll add my answer in my question so I can accept your answer, since it helped me think a lot. :) – KaeL Sep 05 '12 at 04:30

score 2 · Answer 2 · answered Sep 05 '12 at 03:03

2

GROUP_CONCAT function support DISTINCT expression.

SELECT id, GROUP_CONCAT(DISTINCT value) FROM table_name GROUP BY id

answered Sep 05 '12 at 03:03

xdazz

158,678
38
247
274

I'll check this one @xdazz, will get back on a moment. Thanks! – KaeL Sep 05 '12 at 03:08
This is pretty much the answer. – Brandon Aguilar Sep 06 '17 at 03:30

score 0 · Answer 3 · answered Sep 05 '12 at 03:00

0

This should work:

SELECT GROUP_CONCAT(value) FROM (SELECT id, value FROM table GROUP BY id, value) AS d

answered Sep 05 '12 at 03:00

Gavin Towey

3,132
15
11

Thanks Gavin, but I'm thinking if there is a way to use distinct on another column inside the group_concat. Got a very long query, and had no where to go. Using your answer will have a lot of changes. :) – KaeL Sep 05 '12 at 03:07

MySQL Group_Concat Distinct By Id

3 Answers3

SQLFiddle Demo