Solved

Full Text Search CONTAINSTABLE 'OR' Search Ranking

Posted on 2012-03-15
2
486 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

789 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