Oracle PLSQL: RANK Function

RANK Function

This Oracle tutorial explains how to use the Oracle/PLSQL RANK function with syntax and examples.

Description

The Oracle/PLSQL RANK function returns the rank of a value in a group of values. It is very similar to the DENSE_RANK function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, the DENSE_RANK function will always result in consecutive rankings.

The RANK function can be used two ways - as an Aggregate function or as an Analytic function.

RANK Function Syntax #1 - Used as an Aggregate Function

As an Aggregate function, the RANK function returns the rank of a row within a group of rows.

The syntax for the RANK function when used as an Aggregate function is:

RANK( expr1 [, expr2, ... expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, ... expr_n ] )

Parameters or Arguments

expr1

First expression which identifies a unique row in the group.

expr2, ... expr_n

Optional. Additional expressions which identifies a unique row in the group.

Note

• There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.
• The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

Applies To

The RANK function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example (as an Aggregate Function)

Let's look at some Oracle RANK function examples and explore how to use the RANK function in Oracle/PLSQL.

For example:

select RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;

The SQL statement above would return the rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

RANK Function Syntax #2 - Used as an Analytic Function

As an Analytic function, the RANK function returns the rank of each row of a query with respective to the other rows.

The syntax for the RANK function when used as an Analytic function is:

rank() OVER ( [ query_partition_clause] ORDER BY clause )

Applies To

The RANK function can be used in the following versions of Oracle/PLSQL:

• Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example (as an Analytic Function)

select employee_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';

The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the RANK function would return the same rank for both employees. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the DENSE_RANK function which generates consecutive rankings.