Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Need of Ranking
3.
What is the RANK Function in SQL?
3.1.
Syntax: 
3.2.
Examples of RANK Function
3.3.
Estimating Rank without using Partition Function 
3.4.
Estimating Rank using Partition Function
4.
What is DENSE_RANK Function in SQL?
4.1.
Syntax:
4.2.
Examples of DENSE_RANK Function
4.3.
Estimating DENSE_RANK without using Partition Function 
4.4.
Estimating DENSE_RANK using Partition Function 
5.
Difference between RANK and DENSE_RANK
6.
Frequently Asked Questions
6.1.
What is the difference between rank () and Dense_rank ()?
6.2.
What is difference between rank () ROW_NUMBER () and Dense_rank () in SQL?
6.3.
What is Dense_rank ()?
6.4.
What is the difference between rank and Dense_rank in postgresql?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference between RANK and DENSE_RANK

Author Nikunj Goel
0 upvote
Marketing Analytics using Python: Convert data insights-to-strategy
Speaker
Alka Pandey
Manager Data Scientist @

Introduction

Have you ever heard about the ranking of SQL tables? What are the different ways in which we can rank the tables? In this article, we will see what ranking means in SQL and how it is achieved.

We will see the RANK and DENSE_RANK functions in detail. These are the functions used for ranking in SQL. We will also see what are the differences between these two and how we can use them in different ways. Additionally, we will also see the use case of these functions.

Difference Between RANK and DENSE_RANK in SQL

The RANK and DENSE_RANK functions have some similarities but the main difference between RANK and DENSE_RANK is RANK may skip numbers when the same value occurs multiple times and does not return consecutive numbers. for example - 1,2,2,4 and 5. Whereas DENSE_RANK does not skip the same value and returns the consecutive numbers for example - 1,2,2,3,4 and 5

Need of Ranking

Suppose a student is given a dataset with information regarding the exam of 50 students. It would take them some time to determine the top performers. Sorting through all that information is part of the work, which is problematic. But you can solve this issue using Sql queries like RANK and DENSE_RANK. 

Operations that used to take a long time to complete can now be finished in a few seconds. These functions are used to order and assign numerical values when they fall into two entities. Both these functions are always used with the OVER() clause.

For example, a company has five employees with five different salary amounts.

Amounts are like 50 thousand, 45 thousand, 55 thousand, 35 thousand and 42 thousand respectively. Using this function, we got a rank and estimated who received the highest salary.

 We will learn how to use these functions with real-time examples. Then we will understand what the critical difference between both functions is. 

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is the RANK Function in SQL?

The rank function is an SQL function specifically used to calculate each row's ranking depending on the defined attributes and clauses.

Some essential points need to be considered while using the RANK function:

  • The use of the ORDER BY clause is necessary for using the RANK function. 
     
  • PARTITION BY clause can be optional.
     
  • If two records share identical numerical values- they will also share a similar ranking value.
     

This leads to combined results that do not follow any sequential order, leading to irregularity within some values.

Syntax: 

SELECT col_name
RANK() OVER(
[PARTITION BY exp]  // if you want to partition in group
ORDER BY exp [ASC | DESC], [{exp1…}]
AS ‘r’ FROM table_name 


Examples of RANK Function

Let’s see an example, to understand the RANK function better. Let’s consider Score  table which has attributes like name, subject and marks.

Id name subject marks
1 Ninja_1 Maths 85
2 Ninja_2 Maths 50
3 Ninja_3 Science 70
4 Ninja_4 Economics 85
5 Ninja_5 Maths 20
6 Ninja_6 English 92
7 Ninja_7 English 92
8 Ninja_8 Science 89
9 Ninja_9 Maths 63

The above table (score) contains six rows and four columns; now, we will use the RANK function to estimate the students' Rank without using the PARTITION function.

Estimating Rank without using Partition Function 

SELECT 
  name, subject, marks,
  RANK() OVER (
    ORDER BY marks ASC ) AS rank
FROM Result;

 

Output:

name subject marks rank
Ninja_5 Maths 20 1
Ninja_2 Maths 50 2
Ninja_9 Maths 63 3
Ninja_3 Science 70 4
Ninja_1 Maths 85 5
Ninja_4 Economics 85 5
Ninja_8 Science 89 7
Ninja_6 English 92 8
Ninja_7 English 92 8


We can observe that Ninja_1 and Ninja_4 are assigned the same Rank as 5, and Ninja_8, which has different marks, is assigned a new seven instead of 6.

We will implement the RANK function using the Partition clause in descending order.

Estimating Rank using Partition Function

SELECT 
  name, subject, marks,
  RANK() OVER (
    PARTITION BY subject 
    ORDER BY marks ASC ) AS rank
  From Result;

 

Output:

name subject marks rank
Ninja_4 Economics 85 1
Ninja_6 English 92 1
Ninja_7 English 92 1
Ninja_5 Maths 20 1
Ninja_2 Maths 50 2
Ninja_9 Maths 63 3
Ninja_1 Maths 85 4
Ninja_3 Science 70 1
Ninja_8 Science 89 2

We observe that using the PARTITION clause, we can assign ranks based on groups or partitions of data. Then the PARTITION function can help divide the resulting set into smaller groups or sections. In the above case, since there are four students with the subject 'Maths', the student with higher marks is assigned the rank 1 and 2, 3 and 4, respectively.

What is DENSE_RANK Function in SQL?

The DENSE RANK function and the RANK function share similarities. But slightly different from that of the rank function. It produces a Rank continuously without any gap.

Some essential points need to be kept in mind while using the DENSE_RANK function:

  • Rows with identical values receive the same Rank.
     
  • The Rank of subsequent rows increases by one.

Syntax:

SELECT col_name
DENSE_RANK() OVER(
[PARTITION BY exp]  // if you want to partition in group
ORDER BY exp [ASC | DESC], [{exp1…}]
AS ‘r’ FROM table_name 

 

Examples of DENSE_RANK Function

Let’s see an example, to understand the Dense_Rank function better. Let’s consider Score  table which has attributes like name, subject and marks.

Id name subject marks
1 Ninja_1 Maths 85
2 Ninja_2 Maths 50
3 Ninja_3 Science 70
4 Ninja_4 Economics 85
5 Ninja_5 Maths 20
6 Ninja_6 English 92
7 Ninja_7 English 92
8 Ninja_8 Science 89
9 Ninja_9 Maths 63

The above table (score) contains six rows and four columns; for better understanding, let’s see an example using Dense_Rank without the PARTITION clause.

Estimating DENSE_RANK without using Partition Function 

SELECT 
  name, subject, marks,
  DENSE_RANK() OVER (
    ORDER BY marks ASC 
) AS rank
FROM Result;

Output:

name subject marks rank
Ninja_5 Maths 20 1
Ninja_2 Maths 50 2
Ninja_9 Maths 63 3
Ninja_3 Science 70 4
Ninja_1 Maths 85 5
Ninja_4 Economics 85 5
Ninja_8 Science 89 6
Ninja_6 English 92 7
Ninja_7 English 92 7

Since Ninja_1 and Ninja_4 have the same marks, they are given the same Rank. And Ninja_8 is given Rank 6.

Another example is DENSE_RANK with PARTITION clause.

Estimating DENSE_RANK using Partition Function 

 

SELECT 
  name, subject, marks,
  DENSE_RANK() OVER (
    PARTITION BY subject
    ORDER BY marks ASC
  ) AS rank
FROM Result;

Output:

name subject marks rank
Ninja_4 Economics 85 1
Ninja_6 English 92 1
Ninja_7 English 92 1
Ninja_5 Maths 20 1
Ninja_2 Maths 50 2
Ninja_9 Maths 63 3
Ninja_1 Maths 85 4
Ninja_3 Science 70 1
Ninja_8 Science 89 2

Learn more, wc command in linux

Difference between RANK and DENSE_RANK

RANK DENSE_RANK
The next Rank is skipped if two or more rank rows have identical values in the ORDER BY columns. The next Rank is not skipped if two or more rows have the same values in the ORDER BY columns and obtain the same dense Rank.
They do not follow a chronological order because the skipped ranks. They follow a chronological order.
 
Example: If two workers have the same value, for instance, they will both obtain rank 1, and the following employee will receive rank 3. Example: If two workers have the same value, for instance, they will both be assigned rank 1, and the subsequent employee will be given a rank of 2.

A quick summary of SQL ranking function

RANK The rank function is a SQL function specifically used to calculate each row's ranking depending on the defined attributes and clauses. It skips a rank who has the same record values.

DENSE_RANK : The dense_rank function is a SQL function which assigns rank number to each row. It does not skip any rank who has the same record values.

Also read, AMD vs Intel

Frequently Asked Questions

What is the difference between rank () and Dense_rank ()?

RANK() assigns the same rank to rows with equal values, leaving gaps. DENSE_RANK() assigns the same rank to equal values without gaps, resulting in consecutive ranks.

What is difference between rank () ROW_NUMBER () and Dense_rank () in SQL?

  • RANK() assigns the same rank with gaps for equal values.
  • DENSE_RANK() assigns the same rank without gaps for equal values.
  • ROW_NUMBER() assigns a unique rank to each row.

What is Dense_rank ()?

DENSE_RANK() is a SQL window function that assigns a unique rank to each row in the result set, giving the same rank to rows with equal values without gaps.

What is the difference between rank and Dense_rank in postgresql?

In PostgreSQL, RANK() assigns the same rank to rows with equal values and leaves gaps before the next rank, while DENSE_RANK() assigns the same rank to equal values without gaps, creating consecutive rank values for ties.

Conclusion

In conclusion, the RANK DENSE_RANK functions in SQL are powerful queries to handle ranking and identifying patterns in data. We discuss how this function works and how to use it effectively. We can also use partition functions which make them more unique. 

Different people, like analysts and data professionals, are using this technique to make more informed decisions. However, there is much more to learn about this domain, including advanced SQL techniques. Do check out the article below for more information on SQL.

You can find more informative articles or blogs on our platform. You can also practice more coding problems and prepare for interview questions from well-known companies on your platform, Coding Ninjas Studio. 

Live masterclass