We have a code to sort data and paginate the same and render the data to a Jqgrid. The code works fine when it is connected to an SQL server. That is on paginating each page returns distinct data as expected. But on connecting to an oracle server after some point of time the duplicate data are rendered. Both Oracle and SQL server has same data. Parameters in the Jqgrid page and the number of pages are working as expected on the server-side. That is on paging the start point and chunk size is correctly transferred to the server-side. The duplicate values are observed after sorting columns that are of type varchar in the database but hold numeric also. The database status column holds values of 3 and A, after sorting with the status column the duplicate data when the paginating issue is observed. Duplicate data in the sense, that data on page 2 will be the same as data on page 3. Any help will be appreciated. Thanks in advance... Query One:-
select * from ( select row_.*, rownum rownum_ from ( Select x,y,z,status FROM tablename c WHERE status IN('in condition seperated with status') ORDER BY status asc ) row_ where rownum <= 30 ) where rownum_ > 20;
Query Two:-
select * from ( select row_.*, rownum rownum_ from ( Select x,y,z,status FROM tablename c WHERE status IN('in condition seperated with status') ORDER BY status asc ) row_ where rownum <= 20 ) where rownum_ > 10;
Here the query 1 and 2 always return the same results.
Where two or more values in the column of your ORDER BY clause are the same, you must always provide another secondary column to rank. Otherwise, data return has only a probability of fetching correct result as we expect. The possibility of getting a correct answer will be same as rolling a dice. The secondary column must be unique for accurate results. While you might be able to assume that they will sort themselves based on order entered
select * from( select row_.*, rownum rownum_ from( Select x,y,z,status FROM tablename c WHERE status IN('in condition seperated with status') ORDER BY status,x asc ) row_ where rownum <= 30 ) where rownum_ > 20;
Hoping x is a unique value. DBMS_RANDOM.VALUE can also be used in case if is an oracle specific query other than adding extra order by clause