Rank Order

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rakesh - 20 Sep 2004 21:47 GMT

CREATE Table MyTable(
ID int,
Title varchar(1000)
)

I trying to find all records that contains any of the words :
"one," "two," or "three" in title column.

I need to order the result by row that contains most of the words.

Any help will be appreciated.
Thanks,

Rakesh

Reply to this Message

Hilary Cotter - 20 Sep 2004 23:06 GMT

What you are looking for is the hitcount operator which is a count of the
number of matches. SQL FTS does not supply this.

Rank does reflect the number of hits, but if there are a lot of words in the
document/row rank will be depressed. So if you have two docs in which the
words occur 10 times, a shorter doc will have a higher rank.

To get rank use ContainsTable or FreeTextTable. Here is an example of such a
query:

select KeyID, Problem, Cause, Rank from TableName join
FreeTextTable(TableName,*,'SearchPhrase') as a
on a.[key]=KeyID
order by Rank Desc

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

> CREATE Table MyTable(
> ID int,
[quoted text clipped - 10 lines]
>
> Rakesh

Reply to this Message