Oracle Paging issues

Today in the company encountered a paged query bug, query the first page can query to the data, the following also shows no problem! When I query the second page, the display has finished loading, but the page is not a problem!

Debug debugging, found that the first page query to the data no problem, the second page, the data are not found!

On the first page, the console prints the SQL statement:

         Select from select M. *, RowNum row_id from      (SELECT from wkf_business_config) m         WHERE>= 1  where<

second page, the console prints the SQL statement:

 select   Obj_name, Obj_attribute_name, Attribute_type, DES, STS, priority  from   (
    select  m.*  ,rownum row_id  from  (select  obj_name, Obj_attribute_name, Attribute_type, DES, STS, priority from   wkf_business_config) m  where  rownum >=  26  )  where  row_id <  51  

By analyzing and querying the knowledge of Oracle paging, we find that there is a problem with the SQL statement!

Cannot use > (values greater than 1), >= (values greater than or equal to 1), = (values greater than or equal to 1) for rownum, otherwise no result
-So direct use only starts from 1
--rownum >10 no record, because the first one does not meet the removal, the second rownum 1, so never meet the conditions of the record.

Exactly, when querying the second page, the above SQL statement made the problem!

Since you can't >= (a number greater than or equal to 1), I'll let rownum< be more than 1!

SELECTObj_name, Obj_attribute_name, Attribute_type, DES, STS, priority from      ( SELECTM.*, RowNum row_id from             (SELECTObj_name, Obj_attribute_name, Attribute_type, DES, STS, priority fromwkf_business_config) MWHERERowNum<Wuyi) whererow_id>=  - 

When you change the SQL statement to this, you can query the data as required!

Now that you have a problem with the Oracle paging query, just review the knowledge of Oracle paging!

In MySQL, paging queries are simple! A paged query uses the LIMIT keyword for queries. It has two parameters behind it.

The first parameter is the starting position, and the second parameter is the number of entries that need to be displayed per page.

For example: There are 10 records in the commodity table, which now needs to be paginated, showing 3 data per page. Now you need to see the data on the second page. Then the SQL statement that should be used is:

SELECT * FROM Product limit 3, 3; The first parameter calculates the formula: (Number of pages queried-1) * Display entries per page (2-1) *3=3

Paging in Oracle is not that simple! In Oracle, paged queries are implemented through ROWNUM!

Cannot use > (values greater than 1), >= (values greater than or equal to 1), = (values greater than or equal to 1) for rownum, otherwise no result
So the direct use only starts from 1
rownum >10 no record, because the first one does not meet the removal, the second of the rownum 1, so never meet the conditions of the record .
SELECT * FROM student where rownum>=1;

If you want to use rownum not starting from 1, you need to follow the method below
Select a1.* from (select Student.*,rownum rn from student) A1 where RN >5

Paging Query One
SELECT * FROM (select A1.*,rownum rn from (SELECT * from student) A1 where RowNum <=5) where rn>=2;

Paged Query Two
Select a1.* from (select Student.*,rownum rn from student where RowNum <=5) A1 where Rn >=3;

Paging Query Three
Select a1.* from (select Student.*,rownum rn from student) A1 where RN between 3 and 5;

Oracle Paging issues