How does one compare the text field of one record to all the other records in SQL server to return, for example, the top 5 most related records?
An example of the functionality I'm after are the various Related Posts plugins for Wordpress that produce a list of links to posts related to the post currently being viewed.
Cheers, Iain
You might wanna have a look at this SQL2005 Full-Text Indexing good tutorial.
You need to use CONTAINSTABLE , this returns a RANK column you can use to sort by.
SELECT TOP 5 [Key] FROM CONTAINSTABLE ([YourFullText],'SomethingToSearch')
ORDER BY [RANK] DESC
Thanks for these responses. I'm familiar with the referenced functions, but I'm not sure they do what I need. For example:
SELECT P.id, 'Product' AS Type, FT.rank, C.url + '/' + P.url AS url, longTitle, shortTitle, P.description
FROM Products P
INNER JOIN CONTAINSTABLE (Products, (longTitle, shortTitle), '"my text content"') AS FT ON P.id = FT.[key]
LEFT JOIN Product_Categories PC ON P.id = PC.productID
LEFT Join Categories C ON C.id = PC.categoryID
WHERE [primary] = 1
ORDER BY rank DESC
returns only rows with the exact phrase "my text content" - I need rows with only "text" to be returned, but at a lower rank. If I change the query as follows:
SELECT P.id, 'Product' AS Type, FT.rank, C.url + '/' + P.url AS url, longTitle, shortTitle, P.description
FROM Products P
INNER JOIN CONTAINSTABLE (Products, (longTitle, shortTitle), '"my" or "text" or "content"') AS FT ON P.id = FT.[key]
LEFT JOIN Product_Categories PC ON P.id = PC.productID
LEFT Join Categories C ON C.id = PC.categoryID
WHERE [primary] = 1
ORDER BY rank DESC
I get more rows, but rows with all three words don't appear to rank clearly higher than rows with 1 of the words.
Any further thoughts?