Application of ORACLE Analysis function (1) record ranking

Learning PL/SQL for the first time.

In oracle, the three analysis functions RANK,DENSE_RANK and ROW_NUMBER can be used to number the records, and one of the above three methods can be considered in dealing with the ranking of records.

RANK: by numbering each row after the order by, when the comparison fields are equal, the two lines have the same number, and the next record jumps.

DENSE_RANK: records that are sorted in the same way as RANK and compare fields also have the same number, and the next record does not jump and has a consecutive number.

ROW_NUMBER: sort in the same way as RANK. Even if there are records with duplicate conditions, the number is always incremented and has a continuous number.

Establish an emp employee table

Create table emp (empno number primary key, ename varchar2 (20) not null, deptno varchar2 (20) not null, sal number, hiredate date not null)

Now, you need to rank the salaries of employees in different departments in the emp table.

Select E. rank () over (partition by deptno order by sal desc nulls last) rank, DENSE_RANK () over (partition by deptno order by sal desc nulls last) dense, ROW_NUMBER () over (partition by deptno order by sal desc nulls last) row, from emp e order by deptno

The results are as follows:

Comparing the rank ranking and dense ranking of the fifth record, you will get the difference between the hop sign and the non-jump sign, while ROW_NUMBER will always be numbered sequentially, regardless of whether the value of the comparison condition is the same or not. These three different functions are suitable for different business scenarios.

Another: null will rank before other values in the form, using the NULLS LAST statement to make the null value last.