0

Below you see my query. The parameter

'ISABOUT("Windsor Col*" WEIGHT(1.0),"Windsor Col" WEIGHT(0.7),"Windsor*" WEIGHT(0.5),"Col*" WEIGHT(0.5))'

is actually passed in to a stored function that has the same code. This is for autocomplete, and this query is made when the user types "Windsor Col". What's curious though, is why "Windsor Colorado United States" isn't on the top of the list.

Anyone have a fresh pair of eyes that can spot the mistake I'm making? Also, if you have any other suggestions, feel free to comment. I want the user search experience to be as natural and obvious as possible.

EDIT: The first select (Landmarks) searches against the Name column and the second select (Cities) searches against the Extended column.

enter image description here

Caleb Jares
  • 6,163
  • 6
  • 56
  • 83

1 Answers1

1

In Sql Server the rank returned from CONTAINSTABLE is only applicable to the results returned in that particular FT query. In other words comparing the rank from two different CONTAINSTABLE queries is meaningless (even though the text of the query may be the same they are hitting different columns).

StrayCatDBA
  • 2,740
  • 18
  • 25
  • Hmm. How do I get around this? – Caleb Jares Jun 27 '13 at 19:04
  • You can add a dummy column to the the subqueries to put all the landmarks ahead of all the geonamelocations SELECT ..., 1 queryRank FROM... UNION ALL ..., 2 queryRank FROM ... ORDER BY queryRank, Rank – StrayCatDBA Jun 27 '13 at 19:10
  • I'm not sure I understand. In my case I don't want the 3 landmarks ahead of the geonamelocation (Windsor, CO at rank 4). – Caleb Jares Jun 27 '13 at 19:12