I want to return search results in paginated format. However I can't seem to successfully get the first 10 results of my query.

The problem is the 'RowNum' returned are like 405, 687, 1024 etc. I want them to be renumbered as 1,2,3,4,5 etc., so that when I specify between rows 1 and 20 i get the first 20 search results. Instead, because the numbers are larger, I get no results between 1 and 10.

If i change RowNum condition to:

AND RowNum < 20000

I get plenty of results

Here's the sql:

                SELECT  *
            FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY DocumentID ) AS RowNum, *
                      FROM      Table
                    ) AS RowConstrainedResult
            WHERE   RowNum >= 1
                AND RowNum < 20
                                AND Title LIKE '%diabetes%'
                                AND Title LIKE '%risk%'

Any help appreciated.