|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
Functions, 35 of 166
"Analytic Functions" for information on syntax, semantics, and restrictions
DENSE_RANK function computes the rank of a row in an ordered group of rows. 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_RANKcalculates the dense rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the order_by_clause of the aggregate match by position. Therefore, the number of arguments must be the same and types must be compatible.
DENSE_RANKcomputes 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.
The following example computes the ranking of a hypothetical employee with the salary $15,500 and a commission of 5% in the sample table
SELECT DENSE_RANK(15500, .05) WITHIN GROUP (ORDER BY salary DESC, commission_pct) "Dense Rank" FROM employees; Dense Rank ------------------- 3
The following statement selects the department name, employee name, and salary of all employees who work in the
PURCHASING 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
SELECT d.department_name, e.last_name, e.salary, DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank FROM employees e, departments d WHERE e.department_id = d.department_id AND d.department_id IN ('30', '40'); DEPARTMENT_NAME LAST_NAME SALARY DRANK ----------------------- ------------------ ---------- ---------- Purchasing Colmenares 2500 1 Purchasing Himuro 2600 2 Purchasing Tobias 2800 3 Purchasing Baida 2900 4 Purchasing Khoo 3100 5 Purchasing Raphaely 11000 6 Human Resources Marvis 6500