Paginating With Sql Row_number

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, 6

Solution 1:

You need to apply the WHERE in the sub query. I would use a CTE instead (assuming SQL-Server)::

WITH CTE AS
(
  SELECT  T.*, 
     ROW_NUMBER() OVER ( ORDERBY DocumentID ) AS RowNum
  FROMTable T
  WHERE Title LIKE'%diabetes%'AND Title LIKE'%risk%'
)
SELECT*FROM CTE 
WHERE RowNum >=1AND RowNum <20

Solution 2:

You need to move your where condition into the subquery:

SELECT*FROM    ( SELECTROW_NUMBER() OVER ( ORDERBY DocumentID ) AS RowNum, *FROMTablewhere  Title LIKE'%diabetes%'AND Title LIKE'%risk%'
                ) AS RowConstrainedResult
        WHERE   RowNum >=1AND RowNum <20

Therownum vairable is being assign sequentially to all rows. You are not selecting any in the top 20.