Oracle9i SQL Reference
Release 1 (9.0.1)

Part Number A90125-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Functions, 92 of 166


RANK

Aggregate Syntax

rank_aggregate::=


Text description of functions84.gif follows
Text description of rank_aggregate

Analytic Syntax

rank_analytic::=


Text description of functions34a.gif follows
Text description of rank_analytic

See Also:

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

Purpose

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.

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 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 Goyal                 6000                         2
           30 Raphaely             11000                         1
           30 Khoo                  3100                         2
           30 Baida                 2900                         3
           30 Tobias                2800                         4
.
.
.

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback