This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Functional Reactive Programming and have Stephen Blackheath and Anthony Jones on-line!
See this thread for details.
Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Order search results by relevance.

 
ravil baizhiyenov
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone,

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?

Thanks!
 
ravil baizhiyenov
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok.. I guess I asked a question that I am able to answer myself again.

Embarrassing =))

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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ravil,
Thanks for posting the solution for others.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic