wood burning stoves 2.0*
The moose likes JDBC 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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Order search results by relevance." Watch "Order search results by relevance." New topic
Author

Order search results by relevance.

ravil baizhiyenov
Greenhorn

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?

Thanks!
ravil baizhiyenov
Greenhorn

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Ravil,
Thanks for posting the solution for others.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Order search results by relevance.
 
Similar Threads
Data Class Locking Test Doublecheck
MS SQL 2005 Express. Full Text search takes rediculously long
pagination in jsp
Displaying a Single Record with a Comma Separated Column from Multiple Records
Returning array To Method