What is the difference between rank and dense rank function?

 Introduction:

They are used for ranking in the SQL. The main difference is that the rank may skip the numbers when the same values occur multiple times and consecutive numbers are not returned. For an example,5,6,6,8 & 9. Dense rank may not skip the numbers when the same values occur multiple times and consecutive numbers are returned. For an example,5,6,6,7,8&9.

Suppose in the company database the employer has to fire 3000 employees out of 10000 employees. They will analyze the performance of all the employees i.e. 10000 employees. Using this concept, employer will shortlist the 3000 employees whose performance are not like 7000.It is easy for them to fire when it is the cutting time. Operational work which are going to complete in the long time will be going to finish within short time due to this concept. They are used to order and assign numerical values when the task lies or fall within the domain. Both are used with the over () clause.

In the company, top performer salary within top 5 will be between 200000 and 300000 lakhs per month.

Name of employee	Salary(Per Month)	Rank(Salary)
Rahul	290000	1
Sonu	250000	2
Monu	230000	3
Ashish	220000	4
Bhola	210000	5

Using this function, we can find out the highest salary. Both of the functions are having similarities and called as the window functions.

Define Rank function:

It is called as SQL function which is used to calculate ranking of the rows on the basis of certain parameters,attributes,clauses and conditions. Before using this, the certain points need to follow:

• Order by clause should use necessarily.
•  Partition by clause use is optional.
• If the two records share the same numerical values, same ranking value will be provided.

In some scenarios, irregularities may occur due to non-sequential values.

Syntax:

select column_name,RANK() OVER (PARTITION BY exp) 

order by exp[ASC|DESC],[{exp1}]

AS rank from table_name;

• 
  
  
  
  

create table score_card(name varchar2(20),subject varchar2(20),marks int);

INSERT ALL

INTO score_card(name,subject,marks) VALUES ('Rahul','Maths','90')

INTO score_card(name,subject,marks) VALUES ('Ram','Maths','95')

INTO score_card(name,subject,marks) VALUES ('Sohan','Maths','10')

INTO score_card(name,subject,marks) VALUES ('Shyam','Maths','50')

INTO score_card(name,subject,marks) VALUES ('Mohan','Science','75')

INTO score_card(name,subject,marks) VALUES ('Manish','Science','87')

INTO score_card(name,subject,marks) VALUES ('Anil','English','94')

INTO score_card(name,subject,marks) VALUES ('Mani','English','94')

INTO score_card(name,subject,marks) VALUES ('Monu','Economics','95')

SELECT * FROM dual;

select * from score_card;

Measuring Rank without partition by

select name,subject,marks,RANK() OVER (order by marks asc)AS rank

from score_card;

In the output, anil and mani are having same rank in the subject English as 6. Monu is having rank 8 instead of 7 and having different marks in case of excluding Ram.

Measuring Rank using partition function:

select name,subject,marks,RANK() OVER (partition by subject order by marks asc)AS rank

from score_card;

Partition function can divide the resulting set into small group or sections. Rank is assigned on the basis of groups or partition of the data.

In the subject math's as per screenshot, the highest marks obtained is 95 has the highest rank 4. Smallest marks obtained is 10 has the lowest rank 1.

Define Dense Rank Function:

It produces the rank continuously without any gap. Before using this, the certain points need to follow:

• Rows with the same value will receive the same rank.
• Rank of the subsequent or next row will be increased to one from its previous rank.

SYNTAX:

select column_name,DENSE_RANK() OVER (PARTITION BY exp) //if you want partition in group

order by exp[ASC|DESC],[{exp1}]

AS rank from table_name;

Measuring Rank without partition by

In the above output, the subject economics and math's have the same marks i.e. 95. Therefore, it is assigned as the same rank 7. Subject science are having different marks 75 and 87. So,it is assigned different ranks i.e. 3 and 4. Next rank is 5. 

Measuring Rank using partition function:

select name,subject,marks,DENSE_RANK() OVER (partition by subject order by marks asc)AS rank

from score_card;

DIFFERENCE:

RANK	DENSE_RANK
It does not follow the chronological order due to rank skipping.	It follows the chronological order.
If the two or more rows are having the same values, the next rank is skipped.	If the two or more rows are having the same values, the next rank is not skipped.
If the two students have scored the highest mark in mathematics like 100 and obtain the same rank 1, following next employee will have rank3.	If the two students have scored the highest mark in mathematics like 100 and obtain the same rank 1, following next employee will have rank2.

select name,subject,marks,RANK() OVER (order by marks asc)AS rank,Dense_RANK() OVER (order by marks asc) AS dense_rank

from score_card;

select name,subject,marks,RANK() OVER (partition by subject order by marks asc)AS rank,DENSE_RANK() OVER (partition by subject order by marks asc)AS dense_rank

from score_card;