The confusion of sorting when using RowNum for paging under Oracle.

This morning the user called to say that the system sort of problem, a closer look, the problem seems to be in ROWNUM this function.

In Oracle the ROWNUM function is to assign the order of the query results in sequence, so a lot of people use this feature for paging operations, that is, rownum between and 60, this kind of restrictions on the number of return rows of the small trick in normal circumstances are normal, But if you want to sort the returned results, it's going to get messed up.

The following query:

Select page.* from
( select RowNum page_id,
Id
Unit_code,
Name
Code
From lb_sys_twork page
WHERE state=1
ORDER by Unit_code,
Code) Page
Where page.page_id between 10
and to

This is a very common online query mode, although I added an ORDER by Unit_code,code in the subquery , and limit the return recordset 10th to 30 but the return set is not sorted according to what I think, find out why RowNum Page_ ID This pseudo-field is not generated in the final return result set, but is returned in the subquery referencing it, so the result set returned after the order by is added is not in accordance with the ... The order is arranged in such a way. I try to put rownum page_id on the outermost layer, as follows:

Select RowNum page_id, page.* from
( Select ID,
Unit_code,
Name
Code
From lb_sys_twork page
WHERE state=1
ORDER by Unit_code,
Code) Page
Where page.page_id between 10
and to

At this time the query error, said to be unable to find page_id This field, I will page_id the field alias removed, directly with the rownum to do the field, the result set is empty. It was then that I realized why the rownum is called a pseudo-field, and RowNum is a column that is automatically added when the result set is output, according to this principle, if you use where rownum=10 to limit the number of rows returned, you will get an empty result set,rownum> 10 can't wait. It seems that the rownum in the subset or on the outermost layer can not, there is no way to do it?

Or Google helped to find this article, which introduced the minus subtraction set operator,minus is to find the difference between two given data sets, that is to find a data collection, The data for this collection is only present in the previous dataset, which does not exist in the next dataset, which is equivalent to collection 1-Set 2, using this function, I can let the collection 1 return the first 50 data, and the collection 2 returns the first 30, 50-30 is equivalent to the returned 第30-50 data. Query SQL as follows:

SELECTRowNum
Page.*
from(SelectPage.*
from lb_sys_twork page
WHERE page.state=1
ORDER by unit_code,
    Code) page
WHERE rownum < minus SELECT rownum,
     page.*
from (select page.*
from lb_sys_twork page
WHERE page.state=1
ORDER by unit_code,
    code) Page
WHERE rownum <