RANK

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

Example

The following statement ranks the employees within each department based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks. Compare this example with the example for "DENSE_RANK".

SELECT deptno, ename, sal, comm, 
   RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) as rk
   FROM emp;

    DEPTNO ENAME             SAL       COMM         RK
---------- ---------- ---------- ---------- ----------
        10 KING             5000                     1
        10 CLARK            2450                     2
        10 MILLER           1300                     3
        20 SCOTT            3000                     1
        20 FORD             3000                     1
        20 JONES            2975                     3
        20 ADAMS            1100                     4
        20 SMITH             800                     5
        30 BLAKE            2850                     1
        30 ALLEN            1600        300          2
        30 TURNER           1500          0          3
        30 WARD             1250        500          4
        30 MARTIN           1250       1400          5
        30 JAMES             950                     6