Want to Avoid Sorting Full-Text Search Results

I'm using the following SQL Server query, which searches a full-text index and appears to be working correctly. Some additional work is included so the query works with paging.

However, my understanding is that full-text searches return results sorted according to ranking, which would be nice.

But I get an error if I remove the OVER clause near the top. Can anyone tell me how this query could be modified to not resort the results?

DECLARE @StartRow int;
DECLARE @MaxRows int;

SET @StartRow = 0;
SET @MaxRows = 10;

WITH ArtTemp AS
(SEL开发者_JAVA技巧ECT 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 CONTAINS(Article.*,'FORMSOF(INFLECTIONAL,"htmltag")'))

SELECT ArtID,ArtTitle,ArtSlug,CatID,CatTitle,ArtDescription,ArtCreated,ArtUpdated,
ArtUserID,UserName
FROM ArtTemp
WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows)
ORDER BY RowID

Thanks.

I'm really not an expert in FTS but hopefully this helps get you started.

First, ROW_NUMBER requires OVER (ORDER BY xxx) in SQL Server. Even if you tell it to order by a constant value, it still might end up rearranging the results. So, if you depend on row numbering to handle your pagination, you're stuck with some kind of sorting.

When I dig around on FTS for that "return results sorted according to ranking" bit, I find a couple articles that describe ordering by rank. In a nutshell, they say that RANK is a column explicitly returned by CONTAINSTABLE. So if you can't find a way to dig out the results ranking from CONTAINS, you might try joining against CONTAINSTABLE instead and use the RANK column explicitly as your order by value with ROW_NUMBER. Example (syntax may be a little off):

SELECT TOP (@StartRow + @MaxRows)
    ROW_NUMBER() OVER (ORDER BY MyFTS.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,"htmltag")') AS MyFTS

The end result is that you're still sorting, but you're doing so on your rankings.

Also, the MSDN page says that CONTAINSTABLE has an ability to limit results on a TOP N basis, too. Maybe this would also be of use to you.