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_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)

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.

Difference between Rank and dense_rank in tabular format

Rank	Dense_rank
Rank function gives you order within your partition	Dense_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 skipped	There 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)

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