• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

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






0
equentin
Asked:
equentin
  • 2
1 Solution
 
danblakeCommented:
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.
0
 
ATAHACCommented:
you can try this

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
INNER JOIN (
     SELECT TOP 100 tblKeywordMatches.ItemID AS ItemID,ft.Rank AS Rank
     FROM tblKeywordMatches
     INNER JOIN FREETEXTTABLE(tblKeywords, Keyword, ''+@SearchQuery+'') AS ft
     ON tblKeywordMatches.KeywordID = ft.[KEY]
     ORDER BY ft.Rank DESC
     )  AS k
ON k.ItemID = l.ItemID
ORDER BY k.Rank
0
 
danblakeCommented:
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.  
0
 
equentinAuthor Commented:
Thank you both for the help.

atahac's solution gave me what exactly i needed

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now