GROUP_CONCAT and DISTINCT are great, but how do i get rid of these duplicates i still have?

Question

hi, i have a mysql table set up like so:

id   uid   keywords
--   ---   ---
1    20    corporate
2    20    corporate,business,strategy
3    20    corporate,bowser
4    20    flowers
5    20    battleship,corporate,dungeon

what i WANT my output to look like is:

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

but the closest i've gotten is:

SELECT DISTINCT uid, GROUP_CONCAT(DISTINCT keywords ORDER BY keywords DESC) AS keywords
 FROM mytable
 WHERE uid !=0
 GROUP BY uid

which outputs:

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

does anyone have a solution? thanks a ton in advance!

Answer 1

Not sure if MySql has this, but SQL Server has a RANK() OVER PARTITION BY that you can use to assign each result a rank...doing so would allow you to only select those of Rank 1, and discard the rest.

Answer 2

What you're doing isn't possible with pure SQL the way you have your data structured.

No SQL implementation is going to look at "Corporate" and "Corporate, Business" and see them as equal strings. Therefore, distinct won't work.

If you can control the database,

The first thing I would do is change the data setup to be:

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

Better yet would be

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

with a seperate table for keywords

KeywordID    KeywordText
1            Corporate
2            Business

Otherwise you'll need to massage the data in code.

Answer 3

Mmm, your keywords need to be in their own table (one record per keyword). Then you'll be able to do it, because the keywords will then GROUP properly.

Answer 4

You have two options as I see it.

Option 1:

Change the way your store your data (keywords in their own table, join the existing table with the keywords table using a many-to-many relationship). This will allow you to use DISTINCT. DISTINCT doesn't work currently because the query sees "corporate" and "corporate,business,strategy" as two different values.

Option 2:

Write some 'interesting' sql to split up the keywords strings. I don't know what the limits are in MySQL, but SQL in general is not designed for this.