i've got a mysql table setup like so:


id   uid   key phrases

--   ---   ---

1    20    corporate

2    20    corporate,business,strategy

3    20    corporate,bowser

4    20    flowers

5    20    battleship,corporate,dungeon

things i WANT my output to appear like is:


20    corporate,business,strategy,bowser,flowers,battleship,dungeon

however the nearest i have become is:


Choose DISTINCT uid, GROUP_CONCAT(DISTINCT key phrases ORDER BY key phrases DESC) AS key phrases

 FROM mytable

 WHERE uid !=

 GROUP BY uid

which results:


20    corporate,corporate,business,strategy,corporate,bowser,flowers,battleship,corporate,dungeon

does anybody possess a solution? thanks a lot ahead of time!

What you are doing is not possible with pure SQL how you have your computer data structured.

No SQL implementation will take a look at "Corporate" and "Corporate, Business" and find out them as equal strings. Therefore, distinct will not work.

If you're able to control the database,

The very first factor I'd do is alter the data setup to become:

id   uid   keyword     <- note, not keyword**s** - **ONE** value in this column, not a comma delimited list
1    20    corporate
2    20    corporate
2    20    business
2    20    strategy

Even better could be

id   uid   keywordId    
1    20    1
2    20    1
2    20    2
2    20    3

having a seperate table for key phrases

KeywordID    KeywordText
1            Corporate
2            Business

Otherwise you will need to massage the information in code.

Mmm, your key phrases have to be in their own individual table (one record per keyword). Then you will have the ability to get it done, since the key phrases will GROUP correctly.

Unsure if MySql has this, but SQL Server includes a RANK() OVER PARTITION BY which you can use to assign each result a rank...doing this would permit you to only choose individuals of Rank 1, and discard the relaxation.

You've two options when i view it.

Option 1:

Alter the way your store your computer data (key phrases in their own individual table, join the present table using the key phrases table utilizing a many-to-many relationship). This will help you to use DISTINCT. DISTINCT does not work presently since the query sees "corporate" and "corporate,business,strategy" as two different values.

Option 2:

Write some 'interesting' sql to part ways the key phrases strings. I'm not sure exactly what the limits have been in MySQL, but SQL generally isn't created for this.