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 |
---|---|
|
See "Analytic Functions" for information on syntax, semantics, and restrictions. |
|
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 theExpressions
in theOrderByClause
.
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.