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?