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?