Oracle - Rank Function

Rank calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

Consider the following Schema

CREATE TABLE USER_TEST
(
user_id numeric(12),
first_name varchar2(32),
last_name varchar2(32),
age numeric(3),
salary numeric(7)
);

insert into user_test values(1, 'Pardeep', 'Kumar', 26, 20000);
insert into user_test values(2, 'Raj', 'Sharma', 23, 15000);
insert into user_test values(3, 'Jai', 'Singh', 30, 30000);
insert into user_test values(4, 'Rana', 'Pratap', 32, 35000);
insert into user_test values(5, 'Nakul', 'Gupta', 23, 16000);
insert into user_test values(6, 'Ritu', 'Kumar', 22, 10000);
insert into user_test values(7, 'Sita', 'Dikshit', 27, 22000);
insert into user_test values(8, 'Gurpal', 'Bhatia', 38, 49000);
insert into user_test values(9, 'Bhim', 'Kumar', 23, 19000);
insert into user_test values(10, 'Lokesh', 'Shriram', 22, 11500);

Aggregation

As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification.

The constant argument expressions and the expressions provided in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.

Single Column RANK as Aggregation Function

Syntax:

RANK () WITHIN GROUP (ORDER BY NULLS )

Example:
The following query returns the rank for a user with salary 10,000SELECT RANK(20000) WITHIN GROUP
(ORDER BY salary DESC NULLS LAST) SAL_RANK
FROM user_test;

Result: SAL_RANK = 5

Multiple Column RANK as Aggregation Function

RANK () WITHIN GROUP (ORDER BY NULLS )

The following query returns the rank of a user a salary of 15,000 and age 23 years.

SELECT RANK(23, 15000) WITHIN GROUP
(ORDER BY age desc, salary desc) RANK
FROM user_test;

Result: Rank = 8


Analytic

As an analytic function, 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 value_exprs in the order_by_clause.

RANK Analytic Function

RANK () OVER ()

Find the user with the 2nd highest salarySELECT * FROM (SELECT user_id, first_name, last_name, salary, RANK() OVER (ORDER BY salary DESC) userrank
FROM user_test) WHERE userrank = 2;

Result:

USER_ID FIRST_NAME LAST_NAME SALARY USERRANK
4 Rana Pratap 35000 2 RANK() OVER ( )

Arrange table by age and then salary

SELECT user_id,first_name, last_name, age, salary,
RANK() OVER (PARTITION BY age ORDER BY salary DESC) RANK
FROM user_test;

Result:

USER_ID FIRST_NAME LAST_NAME AGE SALARY RANK
10 Lokesh Shriram 22 11500 1
6 Ritu Kumar 22 10000 2
9 Bhim Kumar 23 19000 1
5 Nakul Gupta 23 16000 2
2 Raj Sharma 23 15000 3
1 Pardeep Kumar 26 20000 1
7 Sita Dikshit 27 22000 1
3 Jai Singh 30 30000 1
4 Rana Pratap 32 35000 1
8 Gurpal Bhatia 38 49000 1


Return all users with age 23 ordered by salary

SELECT user_id,first_name, last_name, age, salary,
RANK() OVER (PARTITION BY age ORDER BY salary DESC) RANK
FROM user_test where age = 23;

USER_ID FIRST_NAME LAST_NAME AGE SALARY RANK
9 Bhim Kumar 23 19000 1
5 Nakul Gupta 23 16000 2
2 Raj Sharma 23 15000 3

Although the examples quoted here may looks like that we are paying more cost for the same things that can be done by simple order by, but simple examples have been chosen to make it easy to understand how to use RANK.