> I've got a search page where a user can input a number of different
> search criteria such as location, job type, keywords, pay amount, etc.
> I've got one table that holds all of the data that is being freetext
> searched so that we don't have to do joins. That has helped speed
> things up, but I had been using contains to search the data. Now I
> have learned that in order to sort by rank, I have to use
> containstable. But in my testing, it seems I can only have one
> containstable search condition in my query. This won't work for our
> situation, so I'm curious as to how contains sorts it's returned
> records? If you don't specify how you want them sorted, does it have
> it's own sort of internal ranking that it sorts by or is it just
> random? Thanks.

"Daniel Crichton" <msnews@worldofspack.com> wrote in message
news:%23JTQl8mfGHA.452@TK2MSFTNGP02.phx.gbl...
> vtxr1300 wrote on 23 May 2006 05:56:19 -0700:
>
>> I've got a search page where a user can input a number of different
>> search criteria such as location, job type, keywords, pay amount, etc.
>> I've got one table that holds all of the data that is being freetext
>> searched so that we don't have to do joins. That has helped speed
>> things up, but I had been using contains to search the data. Now I
>> have learned that in order to sort by rank, I have to use
>> containstable. But in my testing, it seems I can only have one
>> containstable search condition in my query. This won't work for our
>> situation, so I'm curious as to how contains sorts it's returned
>> records? If you don't specify how you want them sorted, does it have
>> it's own sort of internal ranking that it sorts by or is it just
>> random? Thanks.
>
> No idea on the contains sorting, but I'm guessing it's based on the
> clustered index on the table.
>
> You should be able to have more than one containstable, but you'll have
> issues with the rank sorting as each row will have 1 rank per
> containstable, so you'll have to work out how you want to deal with that.
> eg.
>
> SELECT * FROM Table AS t
> INNER JOIN CONTAINSTABLE(Table, *, 'word1') AS a ON t.PrimaryKey = a.[KEY]
> INNER JOIN CONTAINSTABLE(Table, *, 'word2') AS b ON t.PrimaryKey = b.[KEY]
>
> Using table aliases as above should let you use CONTAINSTABLE multiple
> times, but it'll likely be inefficient and you'll probably want to look at
> trying to work your search terms into a single clause. With the above
> you'll have 2 ranks, one for each search, so you'll need to do something
> to calculate how to do the overall sort - you could simply add them
> together, or maybe average them, and then sort.
>
> Dan
>