sᴜʀᴇsʜ ᴀᴛᴛᴀ sᴜʀᴇsʜ ᴀᴛᴛᴀ - 1 month ago 4
SQL Question

Pagination query for mssql server 2008 Throwing Incorrect syntax near 'OFFSET'

I am Working on pagination in jsp(and i am new to writing sql).

I done my research and found simple queries from

pagination in SQL server 2008 and how to do pagination in sql server 2008

SELECT * FROM document ORDER BY id OFFSET
"+iPageNo+" ROWS FETCH NEXT 10 ROWS ONLY;


in the above query ipageNo is coming 0 for the first time(even i tried by putting 1 manually)

Still giving the error
Incorrect syntax near 'OFFSET'.


What i missed here ? Thanks in advance.

Answer

Here is my work around and working fine now.

SELECT * FROM   (SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownumber,*
        FROM document)  as somex  WHERE  rownumber >= (1+1)*10-9
                         AND rownumber <=(1+1)*10

In the above query i am replacing (1+1) with (pageNUmber+1).

enter image description here

Please feel free to suggest me if any elegant way available.