RANK

The RANK function is an analytic function that calculates the rank of a value in a group of values.

SQL syntax

RANK () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

RANK has the parameters:

Parameter Description

QueryPartitionClause

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

OrderByClause

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

Description

  • The return type is NUMBER.

  • Rows with equal values for the ranking criteria receive the same rank. TimesTen then adds the number of tied rows to the ties rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

  • 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 expressions in the OrderByClause.

Example

Use the RANK function to rank the first 10 employees in department 80 based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks. 

Command> SELECT first 10 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";
< 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 >
10 rows found.