Oracle analysis function RANK ()

Oracle analysis function RANK ()

RANK () is both an aggregate function and an analysis function.

The syntax is as follows:

Aggregate Function Syntax:

 


Syntax of the analysis function:

 


RANK calculates the level of each value in a group value and returns an integer.

As an aggregate function, the example is as follows:
Select rank (1250, 30) within group (order by sal, deptno) "Rank" FROM emp;

Rank
----------
3

The preceding SQL statement indicates the position of the records whose sal is 1250 and deptno is 30. The records are sorted by sal and deptno.

The results of the original table are as follows:

SQL> select * from emp order by sal, deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7900 james clerk 7698 03-DEC-81 950 30
7369 smith clerk 7902 17-DEC-80 1064.8 20
7521 ward salesman 7698 22-FEB-81 1250 500 30
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7876 adams clerk 7788 23-MAY-87 1464.1 20
7844 turner salesman 7698 08-SEP-81 1500 0 30
7499 allen salesman 7698 20-FEB-81 1600 300 30
7934 miller clerk 7782 23-JAN-82 1730.3 10
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 3260.95 10
7566 jones manager 7839 02-APR-81 3959.73 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------------------------------------------------------------------
7788 scott analyst 7566 19-APR-87 3993 20
7902 ford analyst 7566 03-DEC-81 3993 20
7839 king president 17-NOV-81 6655 10

14 rows selected.


When used as an analysis function, it means to analyze the sorting or grade of each record. The rank () value is the same as the rank value, and the subsequent ranking jumps discontinuous.

SQL> select *
2 from (select deptno,
3 rank () over (partition by deptno order by sal desc) rw,
4 ename, sal
5 from emp) where rw <= 4;

DEPTNO RW ENAME SAL
----------------------------------------
10 1 KING 6655
10 2 CLARK 3260.95
10 3 MILLER 1730.3
20 1 SCOTT 3993
20, 1, FORD 3993
20 3 JONES 3959.73
20 4. ADAMS 1464.1
30 1 BLAKE 2850
30 2 ALLEN 1600
30 3 TURNER 1500
30 4 WARD 1250

DEPTNO RW ENAME SAL
----------------------------------------
30 4 MARTIN 1250

12 rows selected.

Note: As an analysis function, it is different from DENSE_RANK () and ROW_NUMBER.

The value of dense_rank () is the same as that of the same rank, and the subsequent rank does not skip continuously.

The row_number () value is not equal to the same rank, and the subsequent rank does not skip continuously