MySQL order by relevance
mysql, sql-order-by
Solution
I managed to get pretty spot on with this:
SELECT *,
( (1.3 * (MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE))) + (0.6 * (MATCH(txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE)))) AS relevance
FROM content
WHERE (MATCH(strTitle,txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE) )
ORDER BY relevance DESC
Problem
I have a search form which searches a site content table to pull back appropriate results. I want to search the title and content fields and pull back results in order of relevance. Giving highest priority to the title. Say we had a table (tblContent) of ``` intID | strTitle | txtContent 1 | Smith John | Lorem Ipsum 2 | Lorem Ipsum | Lorem John Smith Ipsum 3 | John Smith | Lorem Ipsum Lorem Ipsum 4 | Lorem Ipsum | Lorem Ipsum Lorem Ipsum 5 | Lorem Ipsum | Lorem Ipsum Smith John ``` And you were searching for "John Smith" the results should come back in the order of 3,2,1,5 How is this possible?