Difference between RANK and DENSE_RANK

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:

Learn more, wc command in linux

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.

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.

• MS SQL Server Window Functions
   
• TCL Commands in SQL
   
• SQL clauses and its types

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.