Re: How to get exact matches & better ranked results using freetexttable?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Its hard to tell from looking at your data what data belongs in what
columns, for example in this row

rank ref partno name
75 4126 145-UB BADGE, 7.00 X 7.00

is rank=75, ref=4126, partno=145-UB BADGE and name= 7.00 X 7.00

or is it something else like this
rank=75, ref=4126, partno=145-UB and name=BADGE, 7.00 X 7.00

Did you remove all of the letters and number from your noise word list?

Also perhaps you should just search on partno.

Also I think contains will give you what you are looking for, but its hard
to tell as I don't really understand what your data looks like.


--
Hilary Cotter

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

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



<jasolution@xxxxxxxxx> wrote in message
news:1168698214.665262.131510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a SQL Server 2000 table with a varchar column (these are part
numbers) that contains data like this:

145
145-SC
145-AB
145-2
145-8

The full text catalog also includes columns that hold part name and
part description. I'm using the following query to return results for
product searches:

select ftt.RANK,
products.ref,
products.partno,
products.partname,
products.refparent
from PRODUCTS
INNER JOIN
FREETEXTTABLE(products,*,'145-8') as ftt
ON
ftt.[KEY]=products.ref
order by ftt.rank desc

My results are:

rank ref partno name
278 3854 14 Aqua kit
278 3855 14-SCD Aqua kit
278 3856 14-SCZ Aqua kit
278 3857 14-UG Aqua kit
183 3868 19 Standard T&L Kit
183 3869 19-SCD Standard T&L Kit
183 3870 19-SCZ Standard T&L Kit
183 3871 19-UB Standard T&L Kit
75 4121 145 BADGE, 7.00 X 7.00
75 4122 145-8 BADGE, 7.00 X 7.00
75 4123 145-25 BADGE, 7.00 X 7.00
75 4124 145-SCD BADGE, 7.00 X 7.00
75 4125 145-SCZ BADGE, 7.00 X 7.00
75 4126 145-UB BADGE, 7.00 X 7.00

How can I improve my query to return 145-8 (in this instance) as the
highest ranking result?

Thanks for your help!



.

Relevant Pages