I have a table called "users" that have four fields uid ,profession, country , city
create table users(uid int identity primary key,profession varchar(200),country varchar(200),city varchar(200))
go
insert into users(profession,category,country,city) values('doctor','india','lucknow')
insert into users(profession,country,city) values('mechanic','india','lucknow')
insert into users(profession,country,city) values('hawker','india','new delhi')
insert into users(profession,country,city) values('hawker','india','kanpur')
insert into users(profession,country,city) values('doctor','india','kanpur')
i have created a catalog , and want to search records using free text search.
Suppose a user have specified a value "Doctor in Lucknow" , it should firstly by doctor then by lucknow.
i have used the following query for this
SELECT f.rank, uid ,profession, category , country , city
FROM users,
CONTAINSTABLE(users, *, 'doctor or lucknow') f
WHERE users.uid=f.[key]
ORDER BY rank DESC;
it is returning me following output
48 1 doctor india lucknow
48 2 mechanic india lucknow
48 5 doctor india kanpur
but i want that record number 1 and 5 should come before record number 2.