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






Microsoft SQL Server

Avatar of undefined
Last Comment
equentin

8/22/2022 - Mon
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
ATAHAC

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
danblake

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.  
ASKER
equentin

Thank you both for the help.

atahac's solution gave me what exactly i needed

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy