Row_number () is similar to rownum and more powerful (it can be sorted from 1 on each group)
Rank () is the Skip sorting. When there are two second names, the next is the fourth name (also in each group)
Dense_rank () L is a continuous sorting, with two second names still followed by the third.
In contrast, row_number is a http://www.itpub.net/thread-244881-1-1.html without repeated values based on [deptno] for continuous sorting without repeated sequences: Select deptno, empno, row_number () over (order by deptno) from EMP; skip sorting based on [deptno]: Select deptno, empno, rank () over (order by deptno) from EMP;
Sequential sorting with repeated Sequences Based on [deptno:
Select deptno, empno, dense_rank () over (order by deptno) from EMP; group by [deptno] And then sort by [deptno]: Select deptno, empno, rank () over (partition by deptno order by deptno) from EMP; group by [deptno] And then perform continuous sorting based on [deptno] without repeated sequences: Select deptno, empno, row_number () over (partition by deptno order by deptno) from EMP; groups by [deptno] And then performs skip sorting based on [empno]: Select deptno, empno, rank () over (partition by deptno order by empno) from EMP; SQL> select * from EMP;
Empno ename job Mgr hiredate Sal comm deptno
----------------------------------------------------------------
7369 Smith clerk 7902 800.00 20
7499 Allen salesman 7698 1600.00 300.00 30
7521 ward salesman 7698 1250.00 500.00 30
7566 Jones manager 7839 2975.00 20
7654 Martin salesman 7698 1250.00 1400.00 30
7698 Blake manager 7839 1981/05/01 2850.00 30
7782 Clark manager 7839 2450.00 10
7788 Scott analyst 7566 3000.00 20
7839 King President 1981/11/17 5000.00 10
7844 Turner salesman 7698 1500.00 0.00 30
7876 Adams clerk 7788 1983/01/12 1100.00 20
7900 James Clerk 7698 1981/12/03 950.00 30
7902 Ford analyst 7566 3000.00 20
7934 Miller clerk 7782 1982/01/23 1300.00 10
14 rows selected
SQL>
