Oracle-top-n Analysis ____oracle

Top-n, sort by a rule, and then take the first N rows of data.

RowNum: Pseudo-columns, the data table itself does not have such a column, is the Oracle database for each data table plus columns, you can identify line number, by default, rownum by the main index to sort, if there is no primary index is natural sort.

Paging to Oracle must use RowNum:

Select Employee_id,last_name,salary from
(select RowNum rn,employee_id,last_name,salary from 
employees) E
where E.rn <=pageno*pagesize and e.rn> (pageNo-1) *pagesize

(1) When a rule is not sorted, the table data displays

Select RowNum, employee_id, last_name,salary

(2) descending order according to salary

Select RowNum, employee_id, last_name,salary from Employees order by
salary desc;

It can be found that there are one by one correspondence between rownum (pseudo column) and employee_id.

(3) Now take the top 20 data of salary
[From the above figure, the top 20 salary in the above 9600]

So it's right to write that.

Select RowNum rn, employee_id, last_name,salary from
employees 
where RowNum <= to by
salary desc

Obviously, the result is wrong. This just picks up the first 20 of the data in the table and then rownum it in descending order according to salary.

Whenever a table exists, there is a rownum pseudo column. Then we can sort the salary first, then use this as the base table, and then look for it. The code is as follows:

Select RowNum rn2,rn1, employee_id,last_name,salary from 
(
select RowNum rn1, employee_id, last_name,salary
from the employees order by 
salary desc
)
--rn1 the pseudo columns of the Employees table,
and--rn2 the pseudo columns of the new table;

At this time, RN1, rn2 and employee_id have corresponding relationship, we can according to the value of rn2 to take data.

Select RowNum rn2,rn1, employee_id,last_name,salary from 
(
select RowNum rn1, employee_id, last_name,salary< C12/>from Employees 
ORDER BY salary desc
) where rownum <=---where rn2
20 is not correct, where cannot be used The alias of the Outer rownum column
--where rn1 <= 20 can be written, the RN1 column exists in the base table

At this point, the first 20 data is taken from the salary.

There is an interesting phenomenon, if the where rn1<= 20.

Select RowNum rn2,rn1, employee_id,last_name,salary from 
(
select RowNum rn1, employee_id, last_name,salary From
employees 
-Salary desc
)
where Rn1 <= 20

At this point the relationship between Rn2 and employee_id is quite different from the above figure.

(4) Take the data between 10-20

You might think so, just modify the where.

Select RowNum rn2,rn1, employee_id,last_name,salary from 
(
select RowNum rn1, employee_id, last_name,salary From
employees 
-Salary desc
)
where rownum <= and rownum >10

Sorry, no data.!!!
"RowNum can only be used < <=, not used > >="

Solution: The data detected at this time as a base table, and then the RN2, the pseudo-column as a new table in the real column, you can do <, <=, >, >= operation.

Select Rn2,employee_id,last_name,salary
from (
select RowNum rn2,rn1, employee_id,last_name,salary from 
(select RowNum rn1, employee_id, last_name,salary from Employees order by 
salary desc)
)
where rn2 <= RN2 >10
-Note the rn2 at this time, for the rownum of the intermediate table.

At this point the data is (10,20)

Compare the rownum of three query tables:

Select RowNum rn3, rn2,rn1,employee_id,last_name,salary from
(
select RowNum rn2,rn1, Employee_id,last_name, Salary 
from (select RowNum rn1, employee_id, last_name,salary to employees order by 
salary desc)
where rn2 <= and rn2 >10