What is difference between Rank and Dense_Rank with examples?

In my previous articles I have given the different SQL tutorials. In this article I would like to explain about Difference between Rank and Dense_Rank with examples. The Rank Function 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_numEmployee_nameDepartmentSalary
1AmitOBIEE680000
2RohanOBIEE550000
3RohitCOGNOS430000

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.

Difference between Rank and Dense_Rank
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 NameDepartment NoSalaryRank
      Amit10150001
      Rahul1087002
      Aditya1087002  (Tie assigned same rank)
      Shrey10125004

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 NameDepartment NoSalaryRank
      Amit10150001
      Rahul1087002
      Aditya1087002 (Tie assigned different rank)
      Shrey10125003

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.

Difference between Rank and dense_rank in tabular format

RankDense_rank
Rank function gives you order within your partitionDense_rank function is used to give the consecutive ranking for ordered partition
Ties are assigned to same rank in rank function ( Refer example given in article)The ranks are assigned consecutively to every record of the table
When ties are assigned it will give the same rank and next rank will be skippedThere is no mechanism of tie so the next ranks are not skipped and consecutive ranking has been given to the record
RANK( expr1 [, expr2, … expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, … expr_n ] );Dense_RANK () OVER (PARTITION BY expression ORDER BY expression)
Difference between Rank and Dense_Rank

If you like this article or if you have any issues with the same kindly comment in comments section.

4 Replies to “What is difference between Rank and Dense_Rank with examples?”

  1. Hi Amit,

    I have a doubt. Can Rank be applied on multiple tables?
    I have to rank Emp Id and Emp Name order by Invoice Date, Emp Key and Invoice Nbr.

    Emp Id, Emp Name, Invoice Nbr are from one table.
    Emp key is from one.
    Invoice date is from one table.

    I am not able to do the joins.

    Thanks,
    Bhagya

    1. Hi Bhagyarajan,

      You need to use rank function with partition by clause.Try it with Partition by clause. I dont know the business meaning of your query.If you can explain kindly explain..

      Regards,
      Amit

  2. Very well explained, Amit!

    Few typos i wanted to bring to your notice. In the queries we have mentioned ORDER BY SALARY and missed to include DESC i guess.

    Also, in DENSE_RANK function output, we should show the ranks as different (2 & 3) for the same salary, its showing the same for Tie.

    Very helpful, thanks much!

    1. Hi Nishant,

      For Dense rank it is showing same for tie.Kindly perform the scenarios and let me know. I had performed the scenarios for both the results and after that i written this article.
      I really appreciate it if you perform the scenario and give me details on Complexsql@gmail.com

Comments are closed.