CONTAINSTABLE Column Weighting in FTS 2005

Hi there,
Is it possible with SQL Server 2005 to add weighting to a column in a
CONTAINSTABLE or FULLTEXTTABLE ?
I've got the statment :
SELECT ftt.RANK, [content].id, [content].title, [content].content_data FROM
[content] INNER JOIN CONTAINSTABLE([content], (title, content_data),
'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
What I need to do is ensure that matches in the title column have much
higher ranking than matches in content_data.
I've seen similar posts, but have not seen a definitive yes/no answer for
2005.
Many thanks,
Larry.
Hello Larry,
Do two searches, we also include the job title in the content data. This
is to because the searhc has to match in at least on column, not across columns.
i.e. a search for SQL AND server AND DBA with column title = "SQL DBA" and
content of "SQL Server" would not return as result unless you combined the
data.
SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content]
left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON fttitle.[key]=[content].id
INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON
ftt.[key]=[content].id
ORDER BY
fttitle.rank desc, ftt.rank desc

>
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi there,
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> I've got the statment :
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title,
> content_data), 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY
> ftt.rank desc
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> I've seen similar posts, but have not seen a definitive yes/no answer
> for 2005.
> Many thanks,
> Larry.
|||Hi Simon,
Thanks for the reply.
Does having multiple joins have a big impact on performance ?
Would I need to have another join for every field I wanted to search on in
the table ?
Is there anyway of combining the rankings ?
Many thanks,
Larry.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a50b1e8c92d61906228a8@.msnews.microsoft .com...
> Hello Larry,
> Do two searches, we also include the job title in the content data. This
> is to because the searhc has to match in at least on column, not across
> columns. i.e. a search for SQL AND server AND DBA with column title = "SQL
> DBA" and content of "SQL Server" would not return as result unless you
> combined the data.
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> left JOIN CONTAINSTABLE([content], (title), 'Summary') as fttitle ON
> fttitle.[key]=[content].id
> INNER JOIN CONTAINSTABLE([content], (content_data), 'Summary') as ftt ON
> ftt.[key]=[content].id ORDER BY
> fttitle.rank desc, ftt.rank desc
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Here is an example:
SELECT [KEY], SUM(Rank) AS WeightedRank
FROM
(
SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Address, 'Street')
UNION
select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Realtor, 'Street')
UNION
select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
Notes, 'Street')
) as x
GROUP BY [KEY]
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
"Larry Neylon" <larry@.senior.removethis.co.uk> wrote in message
news:uFfhHyzXHHA.4692@.TK2MSFTNGP04.phx.gbl...
> Hi there,
> Is it possible with SQL Server 2005 to add weighting to a column in a
> CONTAINSTABLE or FULLTEXTTABLE ?
> I've got the statment :
> SELECT ftt.RANK, [content].id, [content].title, [content].content_data
> FROM [content] INNER JOIN CONTAINSTABLE([content], (title, content_data),
> 'Summary') as ftt ON ftt.[key]=[content].id ORDER BY ftt.rank desc
> What I need to do is ensure that matches in the title column have much
> higher ranking than matches in content_data.
> I've seen similar posts, but have not seen a definitive yes/no answer for
> 2005.
> Many thanks,
> Larry.
>
|||Hello Larry,
It depends on the searches and the data in the index. It can do.
But generally the job title will be small and so should be a quck search.
We combine the rankings by in my example doing fttitle.RANK * 100 + ftt.RANK.
This means that a match in the job title will always rank above a match in
the content. This is because rank goes from 0-100
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Hi Simon,
> Thanks for the reply.
> Does having multiple joins have a big impact on performance ?
> Would I need to have another join for every field I wanted to search
> on in the table ?
> Is there anyway of combining the rankings ?
> Many thanks,
> Larry.
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a50b1e8c92d61906228a8@.msnews.microsoft .com...
|||Thanks for that Hilary,
This appears to be pretty close to what I'm after.
The only question I now have is how best to return the actual data in one
SQL statement
The following would give me what I need, but is this the most efficient way
of achieving this ?
SELECT [KEY], SUM(RANK) AS WeightedRank, content.title, content.abstract
FROM
(
SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(content, (title,
keywords), 'Content')
UNION
select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(content,(abstract),
'Content')
UNION
select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(content,(content_data),
'Content')
) AS x INNER JOIN content ON content.id = [KEY]
GROUP BY [KEY], content.title, content.abstract
ORDER BY WeightedRank DESC
Many thanks,
Larry.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23c$xVODYHHA.984@.TK2MSFTNGP04.phx.gbl...
> Here is an example:
> SELECT [KEY], SUM(Rank) AS WeightedRank
> FROM
> (
> SELECT Rank * 5.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Address, 'Street')
> UNION
> select Rank * 3.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Realtor, 'Street')
> UNION
> select Rank * 1.0 as Rank, [KEY] from FREETEXTTABLE(listing,
> Notes, 'Street')
> ) as x
> GROUP BY [KEY]
> --
> 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
>
> "Larry Neylon" <larry@.senior.removethis.co.uk> wrote in message
> news:uFfhHyzXHHA.4692@.TK2MSFTNGP04.phx.gbl...
>