CODEWITHSUNDEEP
mysqldistinctconcat-ws
m mardani
m mardani

Reputation: 11

How to Select DISTINCT CONTACT of some Columns in Mysql

I have a join query that returns data like this :

Column A Column B Column C Column D Column E Column F Column G CONCAT_WS(',',D,E,F,G)
id1 id2 id3 14 15 14 16 14,15,14,16
-------- -------- -------- -------- -------- -------- -------- ----------------------
id5 id6 id7 14 15 17 16 14,15,17,16

I want the last column to only return unique values found in each record. For example, for the first record I want the last column to have values: (14,15,16) instead of 14,15,14,16.

Upvotes: 0

Views: 49

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

sql is row based rather than column based so there is no function which can operate on columns in the way you want.

If you have a unique row identifier you could UNPIVOT using UNION which comes with an implicit distinct , group_concat and join for example

DROP TABLE IF EXISTS T;

create table t
(id int , col1 int,col2 int,col3 int);

insert into t values
(1,1,2,3),(2,1,1,2),(3,3,3,3);

with cte as
(select id,col1 col from t
union
select id,col2 from t
union
select id,col3 from t
)
select *,gc
from t
join (select id,group_concat(col) gc from cte group by id) s on s.id = t.id
order by t.id;

+------+------+------+------+------+-------+-------+
| id   | col1 | col2 | col3 | id   | gc    | gc    |
+------+------+------+------+------+-------+-------+
|    1 |    1 |    2 |    3 |    1 | 1,2,3 | 1,2,3 |
|    2 |    1 |    1 |    2 |    2 | 2,1   | 2,1   |
|    3 |    3 |    3 |    3 |    3 | 3     | 3     |
+------+------+------+------+------+-------+-------+
3 rows in set (0.023 sec)

if you don't have a unique row identifier you might get away with assigning one but there's no guarantee the rows output will be in the same order as the rows input.

with cte as
(select id,col1 col from t
union
select id,col2 from t
union
select id,col3 from t
),
cte1 as
(select id,row_number() over () rn from t)

select *,gc
from cte1 t
join (select id,group_concat(col) gc from cte group by id) s on s.id = t.id
order by t.id;

Both solution look poor performers but that's what happens with poorly built data.

Upvotes: 0

Related Questions