DENSE_RANK

The DENSE_RANK function is an analytic function that computes the rank of rows in an ordered group of rows and returns the ranks as type NUMBER.

SQL syntax

DENSE_RANK () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

DENSE_RANK has the parameters:

Parameter Description

QueryPartitionClause

See "Analytic Functions" for information on syntax, semantics, and restrictions.

OrderByClause

See "Analytic Functions" for information on syntax, semantics, and restrictions.

Description

  • 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. Rows with equal values for the ranking criteria receive the same rank.

  • DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the Expressions in the OrderByClause.

Example

Select the department name, employee name, and salary of all employees who work in the human resources or purchasing department. Compute a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank.

Command> SELECT d.department_name, e.last_name, e.salary, DENSE_RANK()
           OVER (PARTITION BY e.department_id ORDER BY e.salary) AS dense
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
         AND d.department_id IN ('30', '40')
         ORDER BY e.last_name, e.salary, d.department_name, dense;
< Purchasing, Baida, 2900, 4 >
< Purchasing, Colmenares, 2500, 1 >
< Purchasing, Himuro, 2600, 2 >
< Purchasing, Khoo, 3100, 5 >
< Human Resources, Mavris, 6500, 1 >
< Purchasing, Raphaely, 11000, 6 >
< Purchasing, Tobias, 2800, 3 >
7 rows found.