Oracle rank and Dense_rank rank function __c language

1.rank function

Rank calculates the rank of a set of values and returns the numeric type. Rankings may be discontinuous. If there are 5 people, 2 of whom rank first, then rank returns the result: 1 1 3 4 5. As an aggregate function, returns the rank of the virtual row in the sample table. As an analytic function, returns the ranking of each one. 1.1 aggregate function Syntax

1.2 analytic function Syntax

1.3 Examples of aggregate functions

Calculates the rank of an employee who assumes a salary of 2000 in the sample table.

Sql> Select rank within group (order by e.sal Desc) "Rank"
2 from EMP e;

Rank
----------
7

Employees who are supposed to have a salary of 2000 are ranked 7th in the sample form from highest to lowest salary.

1.4 Examples of analytic functions

Rank by salary from highest to lowest:

Sql> Select E.ename, E.sal, Rank () over (order by e.sal Desc) "Rank" from emp e;

Ename SAL Rank
---------- --------- ----------
KING 5000.00 1
FORD 3000.00 2
SCOTT 3000.00 2
JONES 2975.00 4
BLAKE 2850.00 5
CLARK 2450.00 6
ALLEN 1600.00 7
TURNER 1500.00 8
MILLER 1300.00 9
WARD 1250.00 10
MARTIN 1250.00 10
ADAMS 1100.00 12
JAMES 950.00 13
SMITH 800.00 14

The salary is 3000 repetitions, ranked 2nd, skipping 3rd.

The rank of salary from highest to lowest in the ministry:

Sql> Select E.ename,
2 E.sal,
3 E.deptno,
4 rank () over (partition by E.deptno ORDER BY e.sal Desc) "Rank"
5 from EMP E;

ename SAL DEPTNO Rank
---------- --------- ------ ----------
KING 5000.00 10 1
CLARK 2450.00 10 2
MILLER 1300.00 10 3
SCOTT 3000.00 20 1
FORD 3000.00 20 1
JONES 2975.00 20 3
ADAMS 1100.00 20 4
SMITH 800.00 20 5
BLAKE 2850.00 30 1
ALLEN 1600.00 30 2
TURNER 1500.00 30 3
MARTIN 1250.00 30 4
WARD 1250.00 30 4
JAMES 950.00 30 6

2.dense_rank function

Dense_rank calculates the ranking of a set of values, which is a sequential integer starting from 1. If there are 5 people, 2 of whom rank first, then rank returns the result: 1 1 2 3 4. As an aggregate function, returns the rank of the virtual row in the sample table. As an analytic function, returns the ranking of each one. 2.1 aggregate function Syntax

2.2 analytic function Syntax

2.3 Examples of aggregate functions

Calculates the rank of an employee who assumes a salary of 2000 in the sample table.

Sql> Select Dense_rank within group (ORDER by e.sal Desc) "Rank"
2 from EMP e;

Rank
----------
6

Employees who are supposed to have a salary of 2000 are ranked 7th in the sample form from highest to lowest salary.

2.4 Examples of analytic functions

Rank by salary from highest to lowest:

Sql> Select E.ename,
2 E.sal,
3 Dense_rank () over (order by e.sal Desc) "Rank"
4 from EMP E;

Ename SAL Rank
---------- --------- ----------
KING 5000.00 1
FORD 3000.00 2
SCOTT 3000.00 2
JONES 2975.00 3
BLAKE 2850.00 4
CLARK 2450.00 5
ALLEN 1600.00 6
TURNER 1500.00 7
MILLER 1300.00 8
WARD 1250.00 9
MARTIN 1250.00 9
ADAMS 1100.00 10
JAMES 950.00 11
SMITH 800.00 12

Two 1250 were ranked 8th, then 1100 was 9th. Rank is continuous and continuous.

The rank of salary from highest to lowest in the ministry:

Sql> Select E.ename,
2 E.sal,
3 E.deptno,
4 Dense_rank () over (partition by E.deptno ORDER BY e.sal Desc) "Rank"
5 from EMP E;

ename SAL DEPTNO Rank
---------- --------- ------ ----------
KING 5000.00 10 1
CLARK 2450.00 10 2
MILLER 1300.00 10 3
SCOTT 3000.00 20 1
FORD 3000.00 20 1
JONES 2975.00 20 2
ADAMS 1100.00 20 3
SMITH 800.00 20 4
BLAKE 2850.00 30 1
ALLEN 1600.00 30 2
TURNER 1500.00 30 3
MARTIN 1250.00 30 4
WARD 1250.00 30 4
JAMES 950.00 30 5

20 departments have two 3000 and ranked first, then 2975 is second. Rank is continuous and continuous.