Oracle RANK function

Oracle/PLSQL RANK function returns the value rank in the value group. It is very similar to the DENSE_RANK function. However, the RANK function may not return a consecutive ranking if the values under test are the same. Whereas, DENSE_RANK will always result in a consecutive ranking.

The RANK function can be used in two ways – as an aggregate function or as an analytical function.

Syntax #1 RANK function – used as an aggregate function

As an aggregate function, the RANK function returns the rank of a string in a string group.

Syntax for RANK function when used as an aggregate function:

RANK( expr1_id [, expr2_id, ... expr_n_id ] ) WITHIN GROUP ( ORDER BY expr1_id [, expr_2_id, ... expr_n_id ] )

Parameters or arguments

• expr1_id – the first expression that identifies a unique string in the group.
• expr2_id, … expr_n_id – optional. Additional expressions that identify a unique string in the group.

The RANK function returns a numeric value.

The first list of expressions must contain the same number of expressions as in the ORDER BY operator.

Expression lists are the same in positions, so the data types must be compatible between the expressions in the first list and in the ORDER BY operator list.

You can use the RANK function in the following versions of Oracle/PLSQL:

Example (as an aggregation function)

Let’s consider an example of the RANK function and learn how to use the RANK function in Oracle/PLSQL.

select RANK(1000, 500) WITHIN GROUP (ORDER BY salary_id, bonus_id)
from empls;

The SQL query will return the employee’s rank with a salary of $1000 and a bonus of $500 from the employees’ table.

Syntax #2 function RANK – used as an analytical function

As an analytic function, the RANK function returns the rank of each query line corresponding to other lines.

Syntax of the RANK function when used as an analytic function:

rank() OVER ( [ query_partition_clause_id] ORDER BY clause_id )

The RANK function returns a numeric value.

You can use the RANK function in the following versions of Oracle/PLSQL:

Example (as an analytic function)

select empl_name,
salary_id,
RANK() OVER (PARTITION BY depart ORDER BY salary_id)
from empls
where depart = 'Marketing';

The SQL query will return all employees working in the marketing department, and then calculate the rank for each unique salary in the marketing department. If two employees had the same salary, the RANK function will return the same rank for both employees. However, it will cause a gap in the ranks (i.e.: inconsistent ranks). This is fundamentally different from DENSE_RANK which generates consecutive ranks.

Ranking using RANK, DENSE_RANK and ROW_NUMBER : Problem Solving using Analytic Functions