Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
rank_aggregate::=
rank_analytic::=
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions |
RANK
calculates the rank of a value in a group of values. 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.
RANK
calculates the 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 their types must be compatible.RANK
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
.The following example calculates the rank of a hypothetical employee in the sample table hr.employees
with a salary of $15,500 and a commission of 5%:
SELECT RANK(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct) "Rank" FROM employees; Rank ---------- 105
Similarly, the following query returns the rank for a $15,500 salary among the employee salaries:
SELECT RANK(15500) WITHIN GROUP (ORDER BY salary DESC) "Rank of 15500" FROM employees; Rank of 15500 -------------- 4
The following statement ranks the employees in the sample hr
schema 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 department_id, last_name, salary, commission_pct, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC, commission_pct) "Rank" FROM employees; DEPARTMENT_ID LAST_NAME SALARY COMMISSION_PCT Rank ------------- --------------- ---------- -------------- ---------- 10 Whalen 4400 1 20 Hartstein 13000 1 20 Fay 6000 2 30 Raphaely 11000 1 30 Khoo 3100 2 30 Baida 2900 3 30 Tobias 2800 4 . . .