views:

165

answers:

0

I have dynamic sql that perform paging and a full text search using CONTAINSTABLE which works fine. Problem is I would like to use FREETEXTTABLE but weight the rank of some colums over others

Here is my orginal sql and the ranking weight I would like to integrate (I have changed names for privacy reasons)

SELECT * FROM (SELECT TOP 10 Things.ID, ROW_NUMBER() OVER(ORDER BY KEY_TBL.RANK DESC ) AS Row FROM [Things]
INNER JOIN CONTAINSTABLE([Things],(Features,Description,Address),'ISABOUT("cow" weight (.9), "cow" weight(.1))') AS KEY_TBL 
ON [Properties].ID = KEY_TBL.[KEY] WHERE TypeID IN (91, 48, 49, 50, 51, 52, 53)      
AND dbo.FN_CalcDistanceBetweenLocations(51.89249, -8.493376, Latitude, Longitude) <= 2.5 ORDER BY KEY_TBL.RANK DESC ) 
x WHERE x.Row BETWEEN 1 AND 10

Here is what I would like to integrate

select sum(rnk) as weightRankfrom From ( select Rank * 2.0 as rnk, [key] from freetexttable(Things,Address,'cow') union all select Rank * 1.0 as rnk, [key] from freetexttable(Things,(Description,Features),'cow') ) as t group by [key] order by weightRankfrom desc