Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
Win a copy of Pragmatic AI this week in the Artificial Intelligence forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Order search results by relevance.  RSS feed

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?

ravil baizhiyenov
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.
author & internet detective
Posts: 38508
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for posting the solution for others.
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!