Differences and exercises between RowNum and Row_number () in the Oracle database

Specifically for rownum It is the number of the Oracle system order assigned to the rows returned from the query, the first row returned is assigned 1, the second row is two, and so on, this is a field that can be used to limit the total number of rows returned by the query, since rownum always starts with 1. But more than 1 of the natural number in rownum do equal to the judgment is considered false condition, so can not find Rownum=n (n "1 natural number), so find the second line after the record can be used to solve the sub-query method, to sub-query rownum alias; The two methods are similar to those that are less than a certain value, but for a value that is not equal to one or a value to a value, it is easier to rank with row_number () aliases than with rownum pseudo-columns, because pseudo-columns are always searched from the beginning;
See the following code for specific usage and differences;

--Take out the top 5 highest paid

Select Empno,ename,sal,rownum from EMP;

SELECT * FROM (SELECT * from emp ORDER BY sal Desc) where rownum<=5;

SELECT * FROM (select Ename,sal,row_number () over (order by Sal Desc) as num from emp) where num<=5;

SELECT * FROM (select Ename,sal,row_number () over (order by Sal Desc) from EMP) where rownum<=5

--The top 3 wages
SELECT * from emp where Sal >=any (SELECT * FROM (select Sal to emp order by sal Desc) where rownum<=3);

SELECT * FROM (SELECT * from emp ORDER BY sal Desc) where RowNum <4;

SELECT * FROM (select Ename,sal,empno,deptno, Row_number () Up (order by Sal Desc) from EMP) where rownum<4;

SELECT * FROM (select Ename,sal,empno,deptno, Row_number () Up (order by Sal Desc) as num from emp) where num<4


--Sort by salary, take 6th place to 10th place
--using pseudo-columns to get
SELECT * FROM (select Ename,sal,rownum r from (SELECT * to emp ORDER BY sal Desc) where rownum<=10) where r>5;

--Using the rank function to get
SELECT * FROM (select Ename,sal,row_number () over (order by Sal Desc) as num from EMP) where num>5 and num<=10;


-------employees who get the fourth wage rank by salary from high to low

SELECT * FROM (select Ename,sal,row_number () over (order by Sal Desc) as num from emp) where num=4;

SELECT * FROM (select Ename,sal,rownum r from (SELECT * to emp ORDER BY sal Desc) where rownum<=4) where r=4;

"Enterprise Framework Source code" SPRINGMVC MyBatis or Hibernate ehcache level two cache maven non-and MAVEN version "WebSocket Instant Messaging" get ""

Differences and exercises between RowNum and Row_number () in the Oracle database