How to use Rank and Dense Rank in SQL

How to use Rank and Dense Rank in SQL

5 mins read644 Views Comment
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Oct 3, 2023 11:52 IST

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.

2023_01_MicrosoftTeams-image-112-1.jpg

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

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

 
SELECT column_name
RANK () OVER (
PARTITION BY column_name
ORDER BY column_name [ASC|DESC]
)
FROM table_name;
Copy code

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

Example

 
SELECT *
RANK() OVER (
ORDER BY CTC DESC
)
FROM employee;
Copy code

The above query will produce the same result as above.

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

Answer

 
SELECT *
RANK() OVER(
PARTITION BY Department
ORDER BY CTC
)
FROM Employee;
Copy code

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

 
SELECT column_name
DENSE_RANK() OVER(
PARTITION BY column_name
ORDER BY column_name
)
FROM table_name;
Copy code

Example

 
SELECT *
DENSE_RANK() OVER (
ORDER BY CTC DESC
)
FROM employee;
Copy code

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:

 
SELECT *
RANK () OVER (ORDER BY CTC DESC)
DENSE RANK () OVER (ORDER BY CTC DESC)
ROW_NUMBER () OVER (ORDER BY CTC DESC)
FROM employee;
Copy code

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

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio