ORACLE Analytic functions Application (i) ranking of records

Beginner PL/SQL.

In Oracle, the 3 analytic functions of Rank,dense_rank and row_number can be used to number records, and one of the above three methods can be considered in handling record rankings.

RANK: Each row is numbered by the condition after order by, and when the comparison field is equal, the two lines have the same number, and the next record jumps.

Dense_rank: Sort by rank, records that compare fields equal have the same number, and the next record does not jump, with a sequential number.

Row_number: Sort by rank, even if there is a duplicate condition record, the number is always incremented and has a sequential number.

Create an EMP employee table

CREATE TABLE EMP (empno number primary key, ename VARCHAR2 () not NULL, Deptno varchar2 (a) NOT NULL, Sal number, HireDate date not null);

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

Select E.*,rank () over (partition by deptno ORDER BY Sal Desc nulls last) rank ranking, Dense_rank () over (partition B Y deptno ORDER BY sal Desc nulls last) dense ranking, row_number () over (partition by deptno ORDER BY Sal Desc nulls Last) row rank from emp e order by Deptno;

The results are as follows:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/76/85/wKioL1ZVaHDQYKynAAAfkWdmtZA225.png "title=" 2015-11-25_153427.png "alt=" Wkiol1zvahdqykynaaafkwdmtza225.png "/>

Comparing the rank rank of the fifth record with the dense rank will get the difference between the hop number and the non-jumping number, and the row_number will always be numbered sequentially, regardless of whether the value of the comparison condition is the same. These three different functions are suitable for different business scenarios.

Also: null precedes other values in the form, using the NULLS last statement, which causes the null value to be ranked at the end.

This article is from "Bang, Chacaraca" blog, please make sure to keep this source http://10572420.blog.51cto.com/10562420/1716735

ORACLE Analytic functions Application (i) ranking of records