How to use Rank and Dense Rank in SQL

Rank and Dense Rank Functions are the window function used to order values and assign them some number depending on whether they fall in relation to one another. In this article, we will learn how to use rank, dense rank and row number function in sql.

Let you have a sales dataset, and you have to find the top 20 performing assets, or you have to find the best-performing salesman. Then how will you find that?

Method -1: You could arrange and analyze the data manually; that will not be possible if you have millions of records. 

But if you are using SQL, you can sort these data in a few seconds by writing 2-3 lines of queries.

Method-2: Using Rank and Dense Rank Function.

Rank and Dense Rank Functions are the window function used to order values and assign them some number depending on whether they fall in relation to one another.

In this article, we will learn how to rank function, dense rank function, how to use them, and the difference between them. So, without further delay, let’s learn more about rank and dense rank functions.

Must Check: Free Database and SQL Online Course and Certificates

Must Check: SQL Tutorial

Table of Content

• Rank Function
  • Syntax
  • Example
• Dense Rank Function
  • Syntax
  • Example
• Row Number Function

What is Rank Function

The rank function in SQL assigns the rank based on the defined attributes to each row within the given partition. It works with an over clause.

• Ranks in the rank function are determined using the order by clause.
• Ranks in the rank function are given as
  • If two rows have identical numbers, they have the same rank.
  • The rank of the next unidentical number (row) = Rank assigned to the last row + Number of Duplicates.

Now, let’s take an example to get a more understanding of the row function.

Example

Employee ID	Name	Gender	Department	CTC(in Lacs)
1001	Ajay	M	Engineering	25
1002	Babloo	M	Engineering	15
1003	Chhavi	F	HR	23
1004	Dheeraj	M	Admin	25
1005	Evina	F	Engineering	16
1006	Garima	M	Sales	10
1007	Fredy	F	Sales	10
1008	Hans	M	HR	15
1009	Ivanka	F	Engineering	45
1010	Jai	M	Sales	8

The above table (employee) contains 5 columns, now we want to give ranks to the employees depending on their salaries in increasing order.

So, the output will be:

Employee ID	Name	Gender	Department	CTC(in Lacs)	Rank
1009	Ivanka	F	Engineering	45	1
1001	Ajay	M	Engineering	25	2
1004	Dheeraj	M	Admin	25	2
1003	Chhavi	F	HR	23	4
1005	Evina	F	Engineering	16	5
1002	Babloo	M	Engineering	15	6
1008	Hans	M	HR	15	6
1006	Garima	M	Sales	10	8
1007	Fredy	F	Sales	10	8
1010	Jai	M	Sales	8	10

Hope, from the above example you have a clear understanding of how rank function works.

Syntax

Now, let’s see how to implement the above result using SQL.

Example

The above query will produce the same result as above.

Example – 2: Give the rank to employees’ salaries within their departments.

Answer

Output

Employee ID	Name	Gender	Department	CTC(in Lacs)	Rank
1004	Dheeraj	M	Admin	25	1
1009	Ivanka	F	Engineering	45	1
1001	Ajay	M	Engineering	25	2
1005	Evina	F	Engineering	16	3
1002	Babloo	M	Engineering	15	4
1003	Chhavi	F	HR	23	1
1008	Hans	M	HR	15	2
1006	Garima	M	Sales	10	1
1007	Fredy	F	Sales	10	1
1010	Jai	M	Sales	8	3

Now, let’s move to learn how to use the dense rank function.

Must Read: SQL Logical Operator

Dense Rank Function

A dense rank function is similar to the rank function, but with a single difference, it produces the rank without any kind of gap.

If two or more rows have the same value, then:

• They have been assigned the same rank.
• The rank of the next row will be just increased by 1.

Note: It differs from the rank function, as it generates consecutive rank values.

Confused!!

Let’s take an example to clear your doubts.

Example: Find the dense rank of the salaries of employees from the above table.

Employee ID	Name	Gender	Department	CTC(in Lacs)	Dense_Rank
1009	Ivanka	F	Engineering	45	1
1001	Ajay	M	Engineering	25	2
1004	Dheeraj	M	Admin	25	2
1003	Chhavi	F	HR	23	3
1005	Evina	F	Engineering	16	4
1002	Babloo	M	Engineering	15	5
1008	Hans	M	HR	15	5
1006	Garima	M	Sales	10	6
1007	Fredy	F	Sales	10	6
1010	Jai	M	Sales	8	7

Syntax

Example

The above query will produce the same result.

Apart from these two rank functions, we have another function to find the rank in SQL, i.e., row number 

Must Read: SQL LIMITS

What is Row Number

It defines the unique rank to each row of the table, i.e., if two rows have the same rank, then dissimilar to rank and dense_rank, it will give a distinct number to both rows.
Let’s take an example where we use all three: rank, dense_rank, and row_number.

Example: Find the rank, dense rank, and row number of CTC in the above employee table.

Answer:

Output

Employee ID	Name	Gender	Department	CTC(in Lacs)	Rank	Dense_Rank	Row_Number
1009	Ivanka	F	Engineering	45	1	1	1
1001	Ajay	M	Engineering	25	2	2	2
1004	Dheeraj	M	Admin	25	2	2	3
1003	Chhavi	F	HR	23	4	3	4
1005	Evina	F	Engineering	16	5	4	5
1002	Babloo	M	Engineering	15	6	5	6
1008	Hans	M	HR	15	6	5	7
1006	Garima	M	Sales	10	8	6	8
1007	Fredy	F	Sales	10	8	6	9
1010	Jai	M	Sales	8	10	7	10

Conclusion

In this article, we have briefly discussed rank, dense_rank, and row_number functions of sql and how to use them.

Hope you will like the article.

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties