I am trying to order my search results by their relevance... Basically, I have an advanced search form with multiple input fields. Then I query a db table against the matching criteria. (Using Full Text in MS SQL 2005 Express).
In order to sort results by their relevance, I used ORDER BY CASE. Here is a sample query:
Here, as you can see, I select rows that satisfy FREETEXT([description], 'report sudbury').
Then, in ORDER BY clause I first check if the record contains a full search phrase (report sudbury). After I check if it contains both words from the phrase. Then either word, and finally if it contains FREETEXT of the search phrase. In each case I award a satisfying record one point. So, as you see the more relevant records contain more points, so we can now order them by relevance.
Its all okay if the number of words in a search phrase is small (report sudbury in this case)...
The problem is that you have to consider more than n! cases (where n is the number of words in the search phrase) to order records by relevance.
For example: if we have 3 words in the phrase, we need to check the following:
- if the record contains a full phrase
- if the record contains any 2 words from the phrase (could be words 1 AND 2, or 1 AND 3, or 2 AND 3)
- if the record contains one word from the phrase (choices: 1 or 2 or 3)
So this method ends up being not very feasible...
Therefore, my question is: is there a more optimal way to sort records by their relevance?
Joined: Oct 22, 2009
ok.. I guess I asked a question that I am able to answer myself again.
In CASE statements, I just need to check for each word separately... The more matches the record has, the higher its score is. Therefore we will be able to order by the number of matches i.e. relevance...
There we go. Hope someone finds this information useful.