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:
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
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.
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.
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.