It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Order search results by relevance. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Order search results by relevance." Watch "Order search results by relevance." New topic

Order search results by relevance.

ravil baizhiyenov

Joined: Oct 22, 2009
Posts: 16
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

Joined: Oct 22, 2009
Posts: 16
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

Joined: May 26, 2003
Posts: 33113

Thanks for posting the solution for others.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Order search results by relevance.
It's not a secret anymore!