How i get "rank" in this one

Hello
I have this query that works good, but I cant figure out how to get the rank
and order by it desc.
SELECT * FROM Companies WHERE CONTAINS(*,'something')
how I add rank to this one?
TIA
/Lasse
you can't you need to use the contains table syntax for this, ie
SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as FT
on FT.[key]=Companies.pk
where pk is the primary key/unique key of the table you are full text
indexing.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> Hello
> I have this query that works good, but I cant figure out how to get the
rank
> and order by it desc.
> SELECT * FROM Companies WHERE CONTAINS(*,'something')
> how I add rank to this one?
> TIA
> /Lasse
>
|||Hilary,
hmm, one problem, when i search for both Firstname and Lastname using OR it
doesnt really get the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"
is there a way to solve this?
/Lasse
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> you can't you need to use the contains table syntax for this, ie
> SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as
FT
> on FT.[key]=Companies.pk
> where pk is the primary key/unique key of the table you are full text
> indexing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> rank
>
|||Hilary,
this one returns rank=128
5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund Sverige
5472
this one rank=112
3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
Rita Andersson Sverige 3714
when i search for 'rita OR andersson'
not sure i understand how that first one got higher value
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> Hilary,
> hmm, one problem, when i search for both Firstname and Lastname using OR
it
> doesnt really get the "most correct" values first, they get the highest
rank[vbcol=seagreen]
> but...... and if I use AND nothing shows since no columns contain all
> "keywords"
> is there a way to solve this?
> /Lasse
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
as[vbcol=seagreen]
> FT
the
>
|||Lasse,
Adding RANK is easy, understanding the results is a bit more difficult as
you will need to supply additional information about your environment in
order to understand the RANK values that are returned from your FTS queries.
First of all, you should review the SQL Server 2000 BOL title "Full-text
Search Recommendations" and the next to last paragraph on RANK.
Another factor is the number of rows in your table must be "statistically
significantly", i.e.. at least 10,000 rows in order for the query specific
RANK values to return the executants results. If you are testing with a very
small table, then this can explain why the rank values are not what you are
expecting.
As for searching [both Firstname and Lastname using OR it doesn't really get
the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"], this is a different problem as SQL Server 2000 (correct?) does
not search across columns, see KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
Note, while the above KB articles are specific to SQL Server 7.0, they also
apply to SQL Server 2000 as SQL Server 7.0's behavior was fixed to
correspond to the default SQL Server 2000 behavior.
Finally, one way to join multiple columns in one CONTAINSTABLE query and
influence the rank value of one column over another via the weight parameter
is the following:
use Northwind
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, the AND condition between the Primary key (PK) values as you can also
make this an OR clause, but you may need to use a DISTINCT parameter to
remove duplicate results. Keep in mind, that you still need a "statistically
significantly" number of rows to get the best results.
Regards,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:O#mzCy9yEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hilary,
> this one returns rank=128
> 5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund
Sverige
> 5472
> this one rank=112
> 3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
> Rita Andersson Sverige 3714
> when i search for 'rita OR andersson'
> not sure i understand how that first one got higher value
>
>
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> it
> rank
> as
> the
>