Rank function in Oracle | Rank Vs Dense_Rank in SQL

Rank Vs Dense_Rank :

In my previous articles i have given the different SQL tutorials.In This article i will try to explain Rank Vs Dense_Rank in SQL with Real examples.The Rank vs Dense_Rank is the 100% asked interview question in case of interviews.Before that you should know the concept of Rank function with examples.It is important to give the sequence as well as ranking in the table values.Rank function in Oracle returns the rank of values or rank of group of values in the table.It will return the rank for set of values for given partition.As a simple example suppose there is one group which contains values as Name{Rahul,Ram,Amit,Rohit} and if we use the rank partition by and order by Name then the rank will assign like.{1.Amit 2.Rahul 3.Ram 4.Rohit}.So Rank function is used to give the specific rank to the values in the database.But there is one twist;SQL engine assign the same rank to the same values in the group.Means suppose Name group contains {Amit,Amit,Pradnya} as values and we are ranking using partition by order by name then the result should be.{1.Amit 1.Amit 3.Pradnya}.So Dense_rank function comes in to picture.It is showing the same rank for the name Amit.

“Rank Function is used as Aggregate function as well as Analytical Function”

Click here to get 20 interview questions on SQL……

Rank As aggregate Function:

Rank function is used as aggregate function to return the rank of rows in the table within group of rows.If someone needs to find out the rank of specific row in the table then we will use the rank function.

Rank Function Syntax:

RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] );

Real Life Example:

Consider following table:

Employee_num Employee_name Department Salary
1 Amit OBIEE 680000
2 Rohan OBIEE 550000
3 Rohit COGNOS 430000

Question:Find out Rank of Employee whose Department is OBIEE and Name is rohan?

select RANK(Rohan, OBIEE) WITHIN GROUP (ORDER BY Name, Department) from employees;

The Above will return result as 2 if we consider the table given in example.

Rank Vs Dense_Rank

Rank As Analytical Function:

Rank function is used as analytical function in SQL/PLSQL/SQL server which is used to give the rank to the specific record in the table.Rank function is giving you ranking in ordered partitions.Means Ties are assigned to the same values after using the order by clause.So Rank function is not useful where same data is repeated again and again.It is useful in Unique data where user can make partition and order  the data properly.

Syntax of Rank:

RANK () OVER (PARTITION BY expression ORDER BY expression)

Example:

SELECT Employee_Name,Department_No,Salary,RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;

If we consider above query the same rank will be given for same salaried Employees but it will jump to the new rank for next salaried employee.Kindly check following Output

Employee Name Department No Salary Rank
      Amit 10 15000 1
      Rahul 10 8700 2
      Aditya 10 8700 2  (Tie assigned same rank)
      Shrey 10 12500 4

In above table the Tie for Rahul’s salary and Aditya’s salary.So Same rank has been assigned to both of them.

Click here to get SQL Complex Queries important for interview purpose..

Dense_Rank Analytical Function:

Dense Rank analytical function is same as rank but it has assigned the consecutive rank for Tie values in the table.So Disadvantage of rank function  has been overcome in Dense_Rank function.Dense rank function is useful to give the rank for the SQL values in the table.It is not repeating the rank values so these functions are really very useful in development of reports where we require actual rank values.

“Dense_Rank gives consecutive ranking for ordered partitions…”

Syntax of Dense_Rank:

Dense_RANK () OVER (PARTITION BY expression ORDER BY expression)

Example:

SELECT Employee_Name,Department_No,Salary,Dense_RANK() OVER (PARTITION BY Department_No ORDER BY Salary) “Rank” FROM EMPLOYEE;

If we consider above query the different rank will be given for same salaried Employees:

Employee Name Department No Salary Rank
      Amit 10 15000 1
      Rahul 10 8700 2
      Aditya 10 8700 2 (Tie assigned different rank)
      Shrey 10 12500 3

In above output the consecutive rank has been assigned to same salaried Employees also.Hope you will get idea about Rank function in Oracle and Dense_Rank function in Oracle.Hope you like this article on Rank Vs Dense_Rank.If you like this Rank Vs Dense_Rank article kindly comment it in to comment section.

HOME

4 Comments

  1. Bhagyaranjan Ojha October 4, 2018
    • Amit S October 4, 2018
  2. Nishant December 13, 2018
    • Amit S December 13, 2018

Leave a Reply