0

I have a table , it has a column called id_numbers with comma separated numbers

ID | id_numbers 
_______________

1  | 1,2,3,4,5
2  | 2,3,4,5,6
3  | 3,4,5,6,7

I want to get unique id_numbers from every row of table. In my case it should be 

1,2,3,4,5,6,7

I stated with 

SELECT GROUP_CONCAT(id_numbers SEPARATOR ',') FROM my_table

but I am getting duplicate vales here, I searched but I found articles saying that I cannot do this only using MySQL. Is it possible to do this in MySQL? do I have to loop this with PHP using explode() and array_unique()

Please help, I need to do it only using MySQL. Thanks in Advance.

vladkras
  • 14,057
  • 4
  • 37
  • 51
Kanishka Panamaldeniya
  • 16,046
  • 27
  • 109
  • 185
  • 1
    Have you tried adding `DISTINCT` or `GROUP BY`? – Funk Forty Niner Jan 08 '15 at 04:06
  • 1
    possible duplicate of [MySQL DISTINCT on a GROUP\_CONCAT()](http://stackoverflow.com/questions/3083499/mysql-distinct-on-a-group-concat) – Mark Miller Jan 08 '15 at 04:08
  • I want to get unique values from all rows . i tried group by but no use ,because i want column id_numbers values in every row . – Kanishka Panamaldeniya Jan 08 '15 at 04:09
  • @MarkM no , i have comma separated values inside every column before group concat , that question is bit different – Kanishka Panamaldeniya Jan 08 '15 at 04:10
  • 1
    @KanishkaPanamaldeniya no, it's really the same question. The only difference really is that you have comma as a separator, where the other question has spaces. The answer to that question from fthiella demonstrates how to return the specified result using a MySQL SELECT statement. – spencer7593 Jan 08 '15 at 04:13
  • @KanishkaPanamaldeniya I don't think it is, look closer. The popular answers are not right for you but the *question* is identical. Look at the third answer - the one with the least votes - you need to do something like that. The problem is storing comma separated values - this is a bad idea and should be avoided always to prevent the need for elaborate work-arounds to relatively simple problems. – Mark Miller Jan 08 '15 at 04:13

1 Answers1

1

To generate a distinct list of "numbers" from all of your strings:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.id_numbers,',',d.i+1),',',-1) AS n
  FROM mytable t
  JOIN ( SELECT 0 AS i
         UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
       ) d
    ON d.i <= ( CHAR_LENGTH(t.id_numbers) 
              - CHAR_LENGTH(REPLACE(t.id_numbers,',','')) )
 GROUP BY n
 HAVING n <> ''

To put that back into a comma separated list (ARRGGHH! WHY do we want to that?), we can use the query above as an inline view...

SELECT GROUP_CONCAT(q.n ORDER BY q.n+0) AS id_numbers
  FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.id_numbers,',',d.i+1),',',-1) AS n
           FROM mytable t
           JOIN ( SELECT 0 AS i 
                  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
                  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                 ) d
              ON d.i <= ( CHAR_LENGTH(t.id_numbers) 
                        - CHAR_LENGTH(REPLACE(t.id_numbers,',','')) )
           GROUP BY n
           HAVING n <> ''
       ) q

This is essentially the same answer that was given to the question that Mark identified as a possible duplicate.

Rather than explain how all of that that works, I'm going to suggest that you AVOID storing comma separated lists of a values as a string.

I recommend you take a look at Chapter 2 of Bill Karwin's excellent book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming".

http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557

Those articles you saw that said this can't be done in MySQL, those articles are essentially right... you can't do this in SQL because you do not want to do this in SQL. Doing it does violence to the relational database model.

spencer7593
  • 99,718
  • 14
  • 99
  • 122
  • The inline view query **`d`** will need to be extended (or replaced with another rowsource) to return integer values 0,1,2, up to the maximum number of individual "values" stored in the comma separated lists. I've updated the answer to correct the query (it was missing a +0, a +1 and a closing paren.) – spencer7593 Jan 08 '15 at 04:43