Is it possible to do a full-text search in SQL Server 2005, ordering on the number of matches?
For example, let's say I have the following products for sale.
Tennis Racquets Tennis Balls Footballs Hockey Sticks
If I do a search like:
select * from products where freetext(productname,'tennis ball')
The first three results would be returned as they all have "tennis" or "ball". I would like to order the results so that "tennis balls", the one with two matches instead of one, is on top.
Is there a "match ratio" column, or something that can be used to rank how close the match was? Is there a better way to do a full-text search that would allow for ranking?