DENSE_RANK

Syntax

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

Purpose

DENSE_RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows, 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. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties.

Example

The following statement selects the department name, employee name, and salary of all employees who work in the RESEARCH or SALES department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for "RANK".

SELECT dname, ename, sal, DENSE_RANK() 
   OVER (PARTITION BY dname ORDER BY sal) as drank
   FROM emp, dept
   WHERE emp.deptno = dept.deptno 
   AND dname IN ('SALES', 'RESEARCH');

DNAME          ENAME             SAL      DRANK
-------------- ---------- ---------- ----------
RESEARCH       SMITH             800          1
RESEARCH       ADAMS            1100          2
RESEARCH       JONES            2975          3
RESEARCH       FORD             3000          4
RESEARCH       SCOTT            3000          4
SALES          JAMES             950          1
SALES          MARTIN           1250          2
SALES          WARD             1250          2
SALES          TURNER           1500          3
SALES          ALLEN            1600          4
SALES          BLAKE            2850          5