SQL Server 2005: Full-text search, order by matches

Question

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?

Answer 1

You could try FREETEXTTABLE:

SELECT ft.[Rank], p.*
FROM dbo.products AS p 
INNER JOIN FREETEXTTABLE(products, productname, 'tennis ball') AS ft  
ON ft.key_column = p.key_column  
ORDER BY ft.[Rank] DESC;