1

I am trying to get a list of distinct values from the columns out of a table. Each column can contain multiple comma delimited values. I just want to eliminate duplicate values and come up with a list of unique values.

I know how to do this with PHP by grabbing the entire table and then looping the rows and placing the unique values into a unique array.

But can the same thing be done with a MySQL query?

My table looks something like this:

| ID |                  VALUES                    |
---------------------------------------------------
| 1  |    Acadian,Dart,Monarch                    |
| 2  |    Cadillac,Dart,Lincoln,Uplander          |
| 3  |    Acadian,Freestar,Saturn                 |
| 4  |    Cadillac,Uplander                       |
| 5  |    Dart                                    |
| 6  |    Dart,Cadillac,Freestar,Lincoln,Uplander |

So my list of unique VALUES would then contain:

  • Acadian
  • Cadillac
  • Dart
  • Freestar
  • Lincoln
  • Monarch
  • Saturn
  • Uplander

Can this be done with a MySQL call alone, or is there a need for some PHP sorting as well?

Thanks

  • 1
    You could have a PHP script in a `while` loop, and then `explode(",", $var);` to get the contents of each row as an array format – Daryl Gill Dec 20 '12 at 21:15
  • Similar to existing questions, [_Turning a Comma Separated string into individual rows_](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) (albeit SQL Server), and [_MySQL: Split comma separated list into multiple rows_](http://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows). – Jim DeLaHunt Dec 20 '12 at 21:17
  • 1
    @JimDeLaHunt Not really because MSSQL can do recursion. It's more similar to http://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows – AndreKR Dec 20 '12 at 21:19
  • 1
    As you have discovered, storing lists make the data difficult to work with. Unless there is a good reason for this, it is better to normalize the data as Matthew suggested. Then this (and other similar functions) would be a non-issue. – Leigh Dec 20 '12 at 21:59
  • I see Daryl - is that practical for thousands of records? Is it something that is typically done with thousands of records? –  Dec 22 '12 at 10:05
  • Thanks Jim - That link links to another and another question - yet it does look like it eventually gets down to some solution. yet it is looking to me like the answer to this question is "NO" so far. Maybe better just to get a hard answer to this question here, instead of linking people off to other questions and links. - So far I'm seeing that "NO" MySQL can not do this alone. And you are either left looping thorough the table results with PHP OR Having the MySQL creating new tables that then can be sorted. –  Dec 22 '12 at 10:12
  • I agree Leigh - it seemed like it was a good idea to store comma separated values that can be brought into JavaScript as strings or arrays, and used as temporary memory for the javascript to populate HTML drop lists etc. That is why I did it to begin with. For that purpose, it has worked very well, but now that I want to compare and discriminate with those values, I'm left importing the table to PHP to sort. Which brings me to my next question: Is it OK, or common procedure to loop through thousands of records with PHP? Is that too much a burden on the system? Will that slow it down too much? –  Dec 22 '12 at 10:20
  • Never let your feature implementation decide your database schema. DB architecture is it's own field of expertise, yet programmers always keep falling into this trap. – DanMan Dec 25 '12 at 11:22

2 Answers2

1

Why would you store your data like this in a database? You deliberately nullify all the extensive querying features you would want to use a database for in the first place. Instead, have a table like this:

| valueID | groupID |   name     |
----------------------------------
|    1    |    1    |  Acadian   |
|    2    |    1    |  Dart      |
|    3    |    1    |  Monarch   |
|    4    |    2    |  Cadillac  |
|    2    |    2    |  Dart      |

Notice the different valueID for Dart compared to Matthew's suggestion. That's to have same values have the same valueID (you may want to refer to these later on, and you don't want to make the same mistake of not thinking ahead again, do you?). Then make the primary key contain both the valueID and the groupID.

Then, to answer your actual question, you can retrieve all distinct values through this query:

SELECT name FROM mytable GROUP BY valueID

(GROUP BY should perform better here than a DISTINCT since it shouldn't have to do a table scan)

DanMan
  • 11,323
  • 4
  • 40
  • 61
0

I would suggest selecting (and splitting) into a temp table and then making a call against that.

First, there is apparently no split function in MySQL http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ (this is three years old so someone can comment if this has changed?)

Push all of it into a temp table and select from there.

Better would be if it is possible to break these out into a table with this structure:

| ID |                  VALUES                    |AttachedRecordID |
---------------------------------------------------------------------
| 1  |    Acadian                                 |        1        |
| 2  |    Dart                                    |        1        |
| 3  |    Monarch                                 |        1        |
| 4  |    Cadillac                                |        2        |
| 5  |    Dart                                    |        2        |

etc.

Matthew
  • 9,851
  • 4
  • 46
  • 77