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.
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

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.

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.

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:

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:

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.

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:

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:

## Difference between RANK and DENSE_RANK

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.

### 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.