Solved

Full Text Search CONTAINSTABLE 'OR' Search Ranking

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now