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)


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)


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.


About admin 138 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development


  1. In Dense_rank the rank should be 1,2,2,3 instead of 1,2,3,4.
    Dense_rank give same rank for a tie and next rank for the next row unlike normal rank which give same rank for a tie and skips a number.
    so for rank it is 1,2,2,4
    Dense_rank – 1,2,2,3.

    Please correct it, your articles are very useful, Thanks.

11 Trackbacks / Pingbacks

  1. 20 SQL Queries for interview | Complex SQL Queries for Interview
  2. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  3. SQL Functions List,Functions in SQL,SQL Functions example,Aggregate functions,scalar functions,Aggregate functions in sql,scalar functions in sql,functions
  4. Sed Command in Unix with examples | Stream Editor in Unix | Pipe Command with Examples | Head and Tail command with examples
  5. Chmod command in unix | Unix File Permissions | chmod with examples | Chwn command | Chgrp command | UNMASK
  6. SQL Constraints | Constraints in SQL | Primary Key in SQL | Foreign Key in SQL | Check Constraints in SQL | Null Value
  7. Table partition | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  8. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  9. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  10. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  11. Interview Questions for Deloitte | SQL Interview Questions for Deloitte | Interview Questions Asked in Deloitte | Deloitte Interview Questions

Leave a Reply

Your email address will not be published.