| Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
| Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Aggregate Syntax
rank_aggregate::=
Analytic Syntax
rank_analytic::=
Purpose
RANK calculates the rank of a value in a group of values. The return type is NUMBER.
|
See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence |
Rows with equal values for the ranking criteria receive the same rank. Oracle Database then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. This function is useful for top-N and bottom-N reporting.
As an aggregate function, 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.
As an analytic function, 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.
Aggregate Example
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
Analytic Example
The following statement ranks the employees in the sample hr schema in department 80 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 WHERE department_id = 80;
DEPARTMENT_ID LAST_NAME SALARY COMMISSION_PCT Rank
------------- ------------------------- ---------- -------------- ----------
80 Russell 14000 .4 1
80 Partners 13500 .3 2
80 Errazuriz 12000 .3 3
80 Ozer 11500 .25 4
80 Cambrault 11000 .3 5
80 Abel 11000 .3 5
80 Zlotkey 10500 .2 7
80 Vishney 10500 .25 8
80 Bloom 10000 .2 9
80 Tucker 10000 .3 10
80 King 10000 .35 11
80 Fox 9600 .2 12
80 Greene 9500 .15 13
80 Bernstein 9500 .25 14
80 Sully 9500 .35 15
80 Hall 9000 .25 16
80 McEwen 9000 .35 17
80 Hutton 8800 .25 18
80 Taylor 8600 .2 19
80 Livingston 8400 .2 20
80 Olsen 8000 .2 21
80 Smith 8000 .3 22
80 Cambrault 7500 .2 23
80 Doran 7500 .3 24
80 Smith 7400 .15 25
80 Bates 7300 .15 26
80 Marvins 7200 .1 27
80 Tuvault 7000 .15 28
80 Sewall 7000 .25 29
80 Lee 6800 .1 30
80 Ande 6400 .1 31
80 Banda 6200 .1 32
80 Johnson 6200 .1 32
80 Kumar 6100 .1 34
