RANK is one of the vital Analytic functions of Oracle. It is used to get the rank of a value in a group of values. It can also result in the non-consecutive ranking of the rows. The RANK function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i. It can be used both as an analytic function and as an aggregate function.
Syntax 1: To use as an Aggregate function.
RANK ( expr_1, expr_2, .. expr_n ) WITHIN GROUP ( ORDER BY expr_1, expr_2, .. expr_n )
Parameters:
expr_1, expr_2, .. expr_n: They are used to specify the expressions to be used for identifying a unique row in the group.
Syntax 2: To use as an Analytic function.
RANK() OVER ( [ query_partition_clause] ORDER BY clause )
Parameters:
query_partition_clause: It is also an optional parameter which is used to partition the results into groups.
order_by_clause: It is also an optional parameter which is used to order the data within each partition.
Example 1: Using RANK function as an Aggregate function.
select RANK(100, 20) WITHIN GROUP (ORDER BY marks, extra) from students;
Explanation:
Here, we are using the Rank function as an aggregate function. The rank of a student with a total marks of 100 and an extra marks of 20 from within the students table will be returned after the execution of the query.
Example 2: Using RANK function as an Analytic function.
select name, marks, RANK() OVER (PARTITION BY class ORDER BY marks) from students where class = 'Biology';
Explanation:
Here, we are using the Rank function as an analytic function. All the students who are in the ‘Biology’ class will be returned along with the rank for each unique ‘marks’ in the ‘Biology’ class after the execution of the query. For the same ‘marks’ the function would assign the same ranks, thus resulting in a non-consecutive ranking.