Click here to Skip to main content
15,887,844 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
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.
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900