Link to home
Start Free TrialLog in
Avatar of equentin
equentin

asked on

Ordering FTS results by RANK

Hi all ...

The following FTS SQL returns the results i need but the order of the results does not reflect the order of the sub-select statement results. How can i return the results in the exact order returned by the sub-select statment?

For example as it currently stands if the sub-select returns the IDs of (12, 5, 3, 8, 1) the final recordset will ignore that order and return the records in the order of (1, 3, 5, 8, 12).

SELECT l.ItemID, l.Name, l.CategoryID, l.SubCategoryID, l.FileName, c.Category, s.SubCategory
FROM tblSubCategories AS s
INNER JOIN tblLibraryItems AS l ON s.SubCategoryID = l.SubCategoryID
INNER JOIN tblCategories AS c ON l.CategoryID = c.CategoryID
WHERE l.ItemID IN
      (
      SELECT TOP 100 tblKeywordMatches.ItemID
      FROM tblKeywordMatches
      INNER JOIN FREETEXTTABLE(tblKeywords, Keyword, @SearchQuery) AS ft
      ON tblKeywordMatches.KeywordID = ft.[KEY]
      ORDER BY ft.Rank DESC
      )

Can anyone help? :)

Or alternatively can anyone think of a better way of doing this query?

Many thanks ...

eq






Avatar of danblake
danblake

What happens if you specify the sub-query as:
 SELECT tblKeywordMatches.ItemID
     FROM tblKeywordMatches
     INNER JOIN FREETEXTTABLE(tblKeywords, Keyword, ''+@SearchQuery+'',100) AS ft
     ON tblKeywordMatches.KeywordID = ft.[KEY]

This should return the top 100 records within the sub-query of the free-text table, and order them by rank.

The last parameter within the freetexttable (object,search,top_n_by_rank) is top_n_by_rank

When an integer value, n, is specified, FREETEXTTABLE returns only the top n matches, ordered by rank.
ASKER CERTIFIED SOLUTION
Avatar of ATAHAC
ATAHAC

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SELECT l.ItemID, l.Name, l.CategoryID, l.SubCategoryID, l.FileName, c.Category, s.SubCategory
FROM tblSubCategories AS s
INNER JOIN tblLibraryItems AS l ON s.SubCategoryID = l.SubCategoryID
INNER JOIN tblCategories AS c ON l.CategoryID = c.CategoryID
WHERE l.ItemID IN
     (
     SELECT TOP 100 tblKeywordMatches.ItemID
     FROM tblKeywordMatches
     INNER JOIN FREETEXTTABLE(tblKeywords, Keyword, @SearchQuery) AS ft
     ON tblKeywordMatches.KeywordID = ft.[KEY]
     ORDER BY ft.Rank DESC
     ) K
order by K.ItemID

or use the inner join as required.  
Avatar of equentin

ASKER

Thank you both for the help.

atahac's solution gave me what exactly i needed