Paginating with SQL ROW_NUMBER
- by Kelsey Thorpe
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.
