Return page of full-text search results without help?

Question

I have a query similar to the following to return articles matching a full-text search. During production, the text [FULL TEXT SEARCH CRITERIA] is replaced with the actual full-text search expression.

A query is written to return only one page of results.

 WITH ArtTemp AS ( SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY ArtViews DESC) AS RowID, Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle, Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID, [User].UsrDisplayName AS UserName FROM Article INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID INNER JOIN Category ON Subcategory.SubCatID = Category.CatID INNER JOIN [User] ON Article.ArtUserID = [User].UsrID WHERE [FULL TEXT SEARCH CRITERIA] AND Article.ArtApproved = 1 ) SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated, ArtUserID,UserName FROM ArtTemp WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows) ORDER BY RowID 

This works fine, except that a full-text search returns results in order of relevance, but ROW_NUMBER() OVER (ORDER BY ArtViews DESC) resorts to the results. Is there a way to write the same query without resorting to the results?

Answer 1

The correct answer seems to be to use CONTAINSTABLE , which works similarly to CONTAINS , but returns a table instead of the Boolean WHERE .

So, I get something like this.

 WITH ArtTemp AS ( SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY FTS.RANK DESC) AS RowID, Article.ArtID,Article.ArtTitle,Article.ArtSlug,Category.CatID,Category.CatTitle, Article.ArtDescription,Article.ArtCreated,Article.ArtUpdated,Article.ArtUserID, [User].UsrDisplayName AS UserName FROM Article INNER JOIN Subcategory ON Article.ArtSubcategoryID = Subcategory.SubID INNER JOIN Category ON Subcategory.SubCatID = Category.CatID INNER JOIN [User] ON Article.ArtUserID = [User].UsrID INNER JOIN CONTAINSTABLE(Article, *, 'FORMSOF(INFLECTIONAL, abc)') AS FTS ON Article.ArtID = FTS.[KEY] WHERE Article.ArtApproved = 1 ) SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated, ArtUserID,UserName FROM ArtTemp WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows) ORDER BY RowID