Differences between rownum and row_number in Oracle databases: rownumrow_number

Differences between rownum and row_number in Oracle databases: rownumrow_number

It is clear that for rownum, it is the number of the row returned from the query that is sequentially allocated by the oracle system. The first row is allocated with 1, the second row is second, and so on, this field can be used to limit the total number of rows returned by the query, because rownum always starts from 1, but the natural number above 1 is considered as false when rownum is equal to the judgment, therefore, rownum = n (The Natural Number of n in n "1) cannot be found. Therefore, you can use the subquery method to find the record after the second row to obtain the alias for rownum in the subquery; the two methods are similar to each other when the value is smaller than a certain value. However, when a value ranging from one to a certain value is obtained, use the row_number () alias to obtain the ranking, it is much simpler and more convenient than using the rownum pseudo column, because the pseudo column is always searched from the beginning;

For specific usage and differences, see the following code;

-- Retrieve the top 5 employees with the highest salary

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 

-- Top 3 employees

select * from emp where sal >=any(select * from (select sal from 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() over (order by sal desc) from emp) where rownum<4; select * from (select ename,sal,empno,deptno ,row_number() over (order by sal desc) as num from emp) where num<4 

-- Sort by salary and retrieve 6th to 10th

-- Use pseudo columns to obtain

select * from (select ename,sal,rownum r from (select * from emp order by sal desc) where rownum<=10) where r>5; 

-- Use the ranking function to obtain

select * from (select ename,sal,row_number() over(order by sal desc) as num from emp) where num>5 and num<=10; 

------- Employees ranked fourth in 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 * from emp order by sal desc) where rownum<=4) where r=4;

Summary The differences between rownum and row_number () in oracle

Row_number () is an analysis function. The basic syntax is row_number () over (partition by field order by field)

Rownum is a pseudo column.

Select * from dept where rownum <= 3; select * from dept where rownum between 2 and 3; errors will occur because of rownum features (there will be no 2, No 3 without 1) SELECT * FROM (select. *, rownun from dept a) T1 WHERE T1.ROWNUM BETWEEN 2 AND 3; if so, write SELECT * FROM (select. *, rownum rn from dept a) T1 WHERE T1.RN BETWEEN 2 AND 3;

The main difference is that when rownum is used for sorting, the pseudo-column rownum is added to the result set before sorting. The row_number () function () after the sorting clause is included, the row number is sorted first and then calculated.