Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E26088-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

RANK

Aggregate Syntax

rank_aggregate::=

Description of rank_aggregate.gif follows
Description of the illustration rank_aggregate.gif

Analytic Syntax

rank_analytic::=

Description of rank_analytic.gif follows
Description of the illustration rank_analytic.gif

See Also:

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

Purpose

RANK calculates the rank of a value in a group of values. The return type is NUMBER.

See Also:

Table 3-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.

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
ORDER BY department_id, last_name, salary, commission_pct, "Rank";

DEPARTMENT_ID LAST_NAME                     SALARY COMMISSION_PCT       Rank
------------- ------------------------- ---------- -------------- ----------
           80 Abel                           11000             .3          5
           80 Ande                            6400             .1         31
           80 Banda                           6200             .1         32
           80 Bates                           7300            .15         26
           80 Bernstein                       9500            .25         14
           80 Bloom                          10000             .2          9
           80 Cambrault                       7500             .2         23
           80 Cambrault                      11000             .3          5
           80 Doran                           7500             .3         24
           80 Errazuriz                      12000             .3          3
           80 Fox                             9600             .2         12
           80 Greene                          9500            .15         13
           80 Hall                            9000            .25         16
           80 Hutton                          8800            .25         18
           80 Johnson                         6200             .1         32
           80 King                           10000            .35         11
           80 Kumar                           6100             .1         34
           80 Lee                             6800             .1         30
           80 Livingston                      8400             .2         20
           80 Marvins                         7200             .1         27
           80 McEwen                          9000            .35         17
           80 Olsen                           8000             .2         21
           80 Ozer                           11500            .25          4
           80 Partners                       13500             .3          2
           80 Russell                        14000             .4          1
           80 Sewall                          7000            .25         29
           80 Smith                           7400            .15         25
           80 Smith                           8000             .3         22
           80 Sully                           9500            .35         15
           80 Taylor                          8600             .2         19
           80 Tucker                         10000             .3         10
           80 Tuvault                         7000            .15         28
           80 Vishney                        10500            .25          8
           80 Zlotkey                        10500             .2          7