Order by Keyword ranking

I have the following query:
SELECT Distinct DatePosted,p.DateUpdated,Applied = Case when Email is NULL
then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
ftsolutions..position p left outer join applicantPosition a on (a.PositionID
= p.PositionID and Email = 'tscheiderich@.yahoo.com') where p.ClientID = 1234
and Contains(jobDescription,'developer and sales') order by p.DateUpdated
DESC
I want to change the option to order by keyword ranking.
How would I do this?
Do I have to use Containstable to do it or is there a way to do it directly
with this query?
Thanks,
Tom
You have to use ContainsTable - here is my stab at the query - it is hard
for me to write this as I don't really know your schema - post it for a more
complete solution.
SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL
then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
ftsolutions..position p left outer join applicantPosition a on
(a.PositionID
= p.PositionID and Email = 'tscheiderich@.yahoo.com')
join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
as ft on a.PositionID=ft.[key]
where p.ClientID = 1234
and Contains(jobDescription,'developer and sales') order by ft.[rank] desc,
p.DateUpdated DESC
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
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:Ouxp8tTOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I have the following query:
> SELECT Distinct DatePosted,p.DateUpdated,Applied = Case when Email is NULL
> then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
> substring(JobDescription,1,200),Posted =
> replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
> replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
> ftsolutions..position p left outer join applicantPosition a on
(a.PositionID
> = p.PositionID and Email = 'tscheiderich@.yahoo.com') where p.ClientID =
1234
> and Contains(jobDescription,'developer and sales') order by p.DateUpdated
> DESC
> I want to change the option to order by keyword ranking.
> How would I do this?
> Do I have to use Containstable to do it or is there a way to do it
directly
> with this query?
> Thanks,
> Tom
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eBriEMUOFHA.2468@.tk2msftngp13.phx.gbl...
> You have to use ContainsTable - here is my stab at the query - it is hard
> for me to write this as I don't really know your schema - post it for a
> more
> complete solution.
That was exactly what I was looking for.
I wasn't sure how to use the containstable (a little confusing). I am still
confused as to why I need the same filter (developer and sales) in both the
Containstable and Contains verbs. But that is how I always see it.
Thanks,
Tom
> SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL
> then ' ' else '*' end,p.PositionID,JobTitle,JobDescription =
> substring(JobDescription,1,200),Posted =
> replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
> replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location FROM
> ftsolutions..position p left outer join applicantPosition a on
> (a.PositionID
> = p.PositionID and Email = 'tscheiderich@.yahoo.com')
> join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
> as ft on a.PositionID=ft.[key]
> where p.ClientID = 1234
> and Contains(jobDescription,'developer and sales') order by ft.[rank]
> desc,
> p.DateUpdated DESC
>
> --
> 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
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:Ouxp8tTOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> (a.PositionID
> 1234
> directly
>
|||Ouch I am sorry, you don't need the second contains.
Try this
SELECT DatePosted,p.DateUpdated,Applied = Case when Email is NULL then ' '
else '*' end,p.PositionID,JobTitle,JobDescription =
substring(JobDescription,1,200),Posted =
replace(convert(varchar,p.DatePosted,6), ' ',''),lastUpdated =
replace(convert(varchar,p.DateUpdated,6),' ',''), Company,Location
FROM ftsolutions..position p left outer join applicantPosition a on
(a.PositionID= p.PositionID and Email = 'tscheiderich@.yahoo.com')
join ContainsTable(applicantPosition,jobDescription,'de veloper and sales')
as ft on a.PositionID=ft.[key] where p.ClientID = 1234 order by ft.[rank]
desc, p.DateUpdated DESC
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23fGemeUOFHA.1040@.TK2MSFTNGP12.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eBriEMUOFHA.2468@.tk2msftngp13.phx.gbl...
> That was exactly what I was looking for.
> I wasn't sure how to use the containstable (a little confusing). I am
> still confused as to why I need the same filter (developer and sales) in
> both the Containstable and Contains verbs. But that is how I always see
> it.
> Thanks,
> Tom
>
sql