SQL RANK Vs DENSE RANK Function

In SQL Server 2005, the RANK and DENSE RANK procedures were introduced.

Both of these procedures are used to return sequential integers starting at 1 based on the ORDER BY clause's sorting of rows.

Let's take a closer look at these functions with some examples before attempting to comprehend the differences between them.

SQL RANK Function

The Standard Query Language feature RANK can be thought of as a way for a programmer to categorise and assess a group of numbers.

This feature assigns a specific ranking to each number, and when the same number appears twice, the rank is also applied to those two numbers.

However, this feature is unique in that it always skips the following number when assigning a comparable rank to two or more digits.

Example 1 We have two departments, just like in the Employees table.

As a result of the Partition By Clause, all of the records will be divided into two groups.

Employees in the IT department have one partition, while those in the HR department have another.

The data is then sorted in each split depending on the Salary column. Except in the case of a tie, the RANK function assigns an integer sequence number starting at 1 to each record in each division.

In the event of a tie, the same rank is assigned and the ranking is skipped.

Now execute the following code and you will get the output as we discussed in the previous image.

,sqlite,sqlserver

SELECT
    Salary,
    Name,
    Dept,
    RANK() OVER (PARTITION BY
                     Dept
                 ORDER BY
                     Salary DESC
                ) As [RANK]
FROM
    Employee;

In a summary, the RANK function returns an ascending unique number for each row beginning at 1 and for each partition.

When there are duplicates, all duplicate rows receive the same rank , but the next row after the duplicate rows receives the rank it would have had if there were no duplicates.

If there are duplicates, the RANK function skips them.

Output:

Let's say there are two employees with the first highest compensation. There could be two business cases:

You must utilise the RANK function if your business requirement is to not produce any results for the SECOND highest salary.

SQL DENSE RANK

The value of DENSE RANK differs somewhat from the previous one.

In the Standard Query Language, DENSE RANK is a feature that allows a programmer to categorise data without skipping any numbers, even when similar ranks are allowed.

This attribute serves as the element's primary distinguishing trait.

Example 1: On the Salary column, apply for the Order By Clause. As a result, it will assign a ranking based on the Salary column.

Sqlite

SELECT Name, Dept, Salary,
            DENSE_RANK() OVER (ORDER BY Salary DESC) AS [Rank]
FROM Employee;

Output: When you run the above SQL query, you'll get the following results. As you can see in the result, there is no partition, therefore all of the rows are allocated sequential numbers starting at 1, except when there is a tie.

SQL DIFFERRENCE BETWEEN RANK AND DENSE RANK PROCEDURE

Here we wil discuss the main differences between rank and dense rank procedure:-