Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql get distinct tokens from a string list

Tags:

sql

mysql

I have a query in mysql that group concats records and gives a value having redundant tokens. Below is the output of the query:

Problem Area->ACC-HO->ACC-HO->Credit Note (C/N)->Problem description ->Problem description 

But I want the distinct tokens of this string as below

Problem Area->ACC-HO->Credit Note (C/N)->Problem description 

Is there a way to do this in the sql SELECT query itself?

EDIT Here is schema and query Below is my query:

SELECT 
t2.transaction_id AS transaction_id, 
GROUP_CONCAT(
CONCAT(
 t1.display_text,
 '->',
   (CASE (NOT EXISTS (SELECT 1 FROM mst_node a WHERE a.parent_node_id = t1.node_id))
      WHEN 1 THEN t1.display_text ELSE 
      (SELECT b.display_text AS DISPLAY FROM mst_node b 
      WHERE parent_node_id = t2.node_id AND b.display_seq = t2.entered_value) 
     END)
     ) 
  ORDER BY t2.logtime_stamp SEPARATOR '->'
) AS display_text 
FROM

mst_node t1 
  JOIN trn_user_log t2 
    ON t1.app_id = t2.app_id AND t1.node_id = t2.node_id     
WHERE (t1.app_id = 105) 
  AND t1.parent_node_id IS NOT NULL 
  AND t1.save_as_default IS NULL 
GROUP BY transaction_id,
  mobile_no 
ORDER BY t2.transaction_id DESC,
  t2.logtime_stamp,
  t2.mobile_no 
like image 740
DarkKnightFan Avatar asked Sep 04 '25 03:09

DarkKnightFan


1 Answers

In you GROUP CONCAT add DISTINCT so it will only concatenate unique values.

SELECT GROUP_CONCAT(DISTINCT colName),....
FROM   ...
WHERE  ...
GROUP BY ...

SQLFiddle Demo

like image 69
John Woo Avatar answered Sep 06 '25 01:09

John Woo