Solved

Full Text Search CONTAINSTABLE 'OR' Search Ranking

Posted on 2012-03-15
2
485 Views
Last Modified: 2012-03-16
Hi,
I am executing the below query against the DimEmployee table in AdventureWorksDW
I have successfully set up FullText

SELECT
e.EmployeeKey
,e.FirstName
,e.LastName
,e.Title
,a.[RANK]
FROM
CONTAINSTABLE(DimEmployee, Title,'Marketing OR Assistant',10) a,
DimEmployee e
WHERE a.[KEY] = e.EmployeeKey

So I am searching for the top 10 employees who have 'Marketing' or 'Assistant' in their Title.
The following are the results
EmployeeKey      FirstName      LastName      Title      RANK
2      Kevin      Brown      Marketing Assistant      80
7      David      Bradley      Marketing Manager      80
8      David      Bradley      Marketing Manager      80
48      Sariya      Harnpadoungsataya      Marketing Specialist      80
98      William      Vong      Scheduling Assistant      80
99      William      Vong      Scheduling Assistant      80
106      David      Barber      Assistant to the Chief Financial Officer      80
109      Mary      Gibson      Marketing Specialist      80
122      Jill      Williams      Marketing Specialist      80
130      Sean      Chai      Document Control Assistant      80

The RANK returned for each record is 80
Is there a way to return all the same records as above, but that records that have both the words 'Marketing' and 'Assistant' would get a higher rank number?
0
Comment
Question by:Barry Cunney
2 Comments
 
LVL 12

Accepted Solution

by:
patrikt earned 500 total points
ID: 37724665
You have to use weighted term. Rank is counted from weight.

Try this:
CONTAINSTABLE(DimEmployee, Title,'ISABOUT (Marketing weight (.5), Assistant weight (.5) )',10)

Open in new window



Description by MS:
http://msdn.microsoft.com/en-us/library/ms142524.aspx
0
 
LVL 17

Author Closing Comment

by:Barry Cunney
ID: 37728754
Thank you Patrikt
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question