RANK Function in Oracle SQL – PLSQL

The RANK function in Oracle SQL / PLSQL is used to return the rank or position of a value in a group of values. It’s very similar to DENSE_RANK function but RANK function can cause non-consecutive rankings if the tested values are same.

The RANK function can be used in as an Aggregate and Analytical Function.

Syntax for the RANK function as an Aggregate Function in Oracle SQL / PLSQL is:

SELECT RANK(expression1, expression2 . . , expressionN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name;

Syntax for the RANK function as an Analytical Function in Oracle SQL / PLSQL is:

SELECT RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

The number of expressions the RANK function and ORDER BY clause must be the same and also the data types should be compatible.

Example 1:

Using RANK as AGGREGATE function

Suppose we have a table named ‘employee’ as shown below:

If we write our query as:

SELECT RANK(15000) WITHIN GROUP (ORDER BY salary)
FROM employee; 

Will return ‘2’ as the RANK of 15000 because as per the data in the ‘employee’ table if we sort the ‘salary’ column from lowest to highest salary then 15000 will come at the 2nd position in that list.

Example 2:

Using RANK as ANALYTICAL function

Syntax for the RANK function as an Analytical Function in Oracle SQL / PLSQL is:

SELECT RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

Suppose we have a table named ‘employee’ as shown below:

If we write our query as:

SELECT employee_name
       ,salary
       ,department
       ,RANK() OVER (PARTITION BY department ORDER BY salary) Ranking
FROM employee;

Here we can see that RANK function is being used as an analytical function and it returns the position or rank of records in group of records partitioned by a criteria e.g. in our case we have partitioned the records by ‘department’ and then within that partition we are ranking the records relative to each other.

‘Emp B’ is having a rank of 1 and ‘Emp C’ is having a rank 2 in ‘IT’ partition because we have sorted the list by ‘Salary’ and ‘Emp B’s salary’ is less than ‘Emp C’s salary’ within the ‘IT’ partition.

Similarly with in ‘Sales’ partition ‘Emp A’ is having less salary than ‘Emp E’ that’s why ‘Emp A’ is having rank 1 and ‘Emp E’ having rank 2.