I am doing pagination in oracle with Row_Number, I have 40 records in a table and I am applying paging on it(just for testing these are 40,in real they will be in millions).
This is my query
<pre lang="SQL"> SELECT * FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY id) AS rnum FROM mytable)WHERE rnum BETWEEN ((:pageNumber-1) * :pageSize) and (:pageNumber * :pageSize)
When I am running it with pageNumber=1 and pageSize=3, its returning me 3 records,but when I run it with pageNumber=2 and pageSize=3, it is returning me 4 records including one record from pagenumber=1 recordset.
Infact, Its returning 3 records only on pagenumber=1;
How can I fix this issue?Is there any alternate and better approach for paging?My application is in asp.net MVC,I cant keep records in session or cache.
What I have tried:
When I am running it with pageNumber=1 and pageSize=3, its returning me 3 records,but when I run it with pageNumber=2 and pageSize=3, it is returning me 4 records including one record from pagenumber=1 recordset.
Infact, Its returning 3 records only on pagenumber=1;
How can I fix this issue?Is there any alternate and better approach for paging?My application is in asp.net MVC,I cant keep records in session or cache.