what is the mysql query to perform frequency ranking

Question

Hi, am looking for a query which will perform frequency ranking, so that the record which has maximum occurances of supplied "key word" will be listed top in the result.

my fields in the table are text and id. Can any one help me in this. Thanks in advance.

Answer 1

How about this: replace the keyword with nothing, then compare the new size to the old size. The difference, divided by the length of the keyword, is equal to the number of times it was found.

select id, text, 
    (length(text)-length(replace(text, 'KEYWORD', '')))/length('KEYWORD') 
from TableName 
where text like '%KEYWORD%';

This seems kind of flaky to me, but since you are trying to do it with SQL I could not think of a SQL string comparison function that returns the number of times a string was found.