Full text indexing result ranking

[NewsBot]

I'm looking the best/correct way to get the correct result ranking from a full text index search in SQL-2005

 

For this example I have a simple user table with the coulmns [Title, firstname, surname, EmpNumber, Comment] and I need to be able to deal with a seach query like 'Laura Levy', Levey', 'Something from the comments field'.

 

Now this is nice and simple I can just create an full-text index on the users table and issue a*FREETEXT query like

 

SELECT* FROM users AS u

** INNERJOINFREETEXTTABLE(janus_users,(title, firstname, surname, empnumber, comment),'*laura levy')as FT_KEY

***** ON u.userId = FT_KEY.[KEY]

** ORDERBY FT_KEY.[Rank] DESC

And I get the results that I want, but the problem is*the user 'Laura Levy' is*burried down at result #25, the first 38 all having the same result ranking even though there is one result row that has both terms in it's columns.

 

So I've ended up mashing all the columns into a single column in a view and indexing he view.* When I search on that, I get similar results but laura is now at row#1 with a ranking of 189 and row #2s*ranking is*way down with a ranking of 104.

 

So really the question is how can I get the same results without the overhead of the view in the middle?* is there a way to weight the results so that if both seach terms are found in a row it ranks higher?

 

Cheers,

 

Stephen.

 

More...

 

View All Our Microsoft Related Feeds