1

I am having a table like below:

CREATE TABLE [dbo].[test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [area] [varchar](50) NULL,
    [designation] [varchar](100) NULL,
 CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)

id is the primary key in test_table and this table is having full text indexes on name, area and designation columns.

Now I am using a full text query like below:

SELECT test_table.*, ftt.RANK FROM test_table 

INNER JOIN 

FREETEXTTABLE(test_table, (name,area,designation), 'ranjit trail engineer') as ftt
ON
ftt.[KEY]=test_table.id

ORDER BY ftt.RANK DESC

I get the results like this:

id,    name,           area,          designation ,      rank

84829,"Ranjit Barasa","marine drive","software engineer",24

85323,"Jimmy David","hayes road",   "software engineer", 13

85223, "Arthur Philip","center trail","Quality Engineer",13

As you can see above, row 3 with name as 'Arthur Philip' is having same rank as row 2 with name 'Jimmy David' though row 3 has more matches.(row 3 matched with word 'engineer' and 'trail' but still has same rank as row 2)

Can anyone help on how to solve this issue?

1

Since you are using FREETEXTTABLE the ranking is very loose because its interpretation of the string is loose.

The text you supplied for row 2 only has one direct match ("Engineer"), but row 3 has 2 matches("trail" and "Engineer").

But you need to understand the difference between FREETEXT and FREETEXTTTABLE operators and the more precise CONTAINS and CONTAINSTABLE operators.

See https://msdn.microsoft.com/en-us/library/ms176078.aspx which explains in part:

• Generates inflectional forms of the words (stemming).

• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Because FREETEXT freely interprets your data using a thesaurus it is likely that "trail" and "road" are interpreted as equivalent in meaning and therefore the two rows get the same score.

If you need more precise answers you should use CONTAINSTABLE.

  • 1
    I tried containstable too:SELECT FT_TBL.*, KEY_TBL.RANK FROM test_table AS FT_TBL INNER JOIN CONTAINSTABLE (test_table, (*), 'ISABOUT ( "ranjit senior engineer" WEIGHT (1) , "senior engineer" WEIGHT (0.8) , "ranjit engineer" WEIGHT (0.8) , "ranjit" WEIGHT (0.5), engineer weight(0.5) )' ) AS KEY_TBL ON FT_TBL.id = KEY_TBL.[KEY] order by key_tbl.rank desc but records do not show correct ranks even with this. record which has 'ranjit' and 'engineer' showed as first and then records who have 'engineer' alone or both 'senior' and 'engineer' have samerank – karuna Feb 3 '15 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.