Sorting words by their presence in the database, matching similar words
I have a mysql table, there are many words on it.
- Hai.
- Am i
- hai
- Joe
- it
- Those,
- hai
In the above example, "hi" happens three times. I want to create a query that will look at this table and sort the most common words.
You need to use GROUP BY and COUNT
.
SELECT word, COUNT(*) count FROM your_table GROUP BY word ORDER BY count DESC LIMIT 0,3;
Possible way out:
+------+-------+
| word | count |
+------+-------+
| hai | 2 |
| Hai. | 1 |
| This | 1 |
+------+-------+
If you want MySQL to treat hai
both Hai.
as the same word, remove all non-alpha characters before grouping. See MySQL: How to remove all non-alphabetic numeric characters from a string? ... Then, based on the method in this answer , it looks like this:
SELECT LOWER(alphanum(word)) word, COUNT(*) count FROM your_table
GROUP BY LOWER(alphanum(word)) ORDER BY count DESC LIMIT 0,3;
And the possible result:
+------+-------+
| word | count |
+------+-------+
| hai | 3 |
| this | 1 |
| joe | 1 |
+------+-------+
You need to link the sql statement using a method group by
that will group things together. Something like this should get you started.
select word, count(word)
from table
group by word
order by count(word) desc