Rank function in SQL | Rank Vs Dense_Rank | Rank function with examples

Rank function in SQL:

It is important to give the sequence as well as ranking in the table values.Rank function in SQL 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 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 3 (Tie assigned different rank)
      Shrey 10 12500 4

In above output the consecutive rank has been assigned to same salaried Employees also.Hope you will get idea about Rank function in SQL and Dense_Rank function in SQL.If You have any issues suggestion please comment.


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

12 Trackbacks / Pingbacks

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

Comments are closed.