RANK and DENSE RANK Analytic Functions in Oracle SQL

RANK and DENSE RANK Analytic Functions in Oracle SQL

 

RANK
RANK function is used to calculate the rank value from your data. If you have same data in two rows then rank value is same returned by RANK Functions.

Example:
You use the HR schema and find the rank value of the employees on basis of salary column from highest to lowest salary wise rank. If you have two employees same salary then it will return the same rank value for both employees but next order value for employee will be same as row position, it will skip in-between position.
As show in below example:
Rank 3 has two position then next rank value go directly to 5.

SELECT department_id, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary desc) RANK
FROM employees where department_id = 60
ORDER BY department_id,RANK,last_name;

DEPARTMENT_ID  LAST_NAME     SALARY    RANK
------------- ----------    -------  ------
           60     Hunold       9000       1
           60      Ernst       6000       2
           60     Austin       4800       3
           60  Pataballa       4800       3
           60    Lorentz       4200       5

 

DENSE_RANK
DENSE_RANK analytic function is also used to calculate the rank value from your data. It will return the same rank for same value but next value is in sequence.
It assigns consecutive ranks, it will not skip rank if same value came in-between data.
Example
You use the HR schema and find the rank value of the employees on basis of salary column from highest to lowest salary wise rank. If you have two employees same salary then it will return the same rank value for both employees
but next order value for employee rank in Sequential order even duplicate values in between data. It will give next value of rank.
As shown in Example:
It will go to 4 rank after assigning 3 rank to two employee of same salary.

SELECT department_id, last_name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary desc) RANK
FROM employees where department_id = 60
ORDER BY department_id,RANK,last_name;

  
  DEPARTMENT_ID  LAST_NAME     SALARY    RANK
------------- ----------    -------  ------
           60     Hunold       9000       1
           60      Ernst       6000       2
           60     Austin       4800       3
           60  Pataballa       4800       3
           60    Lorentz       4200       4

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.