ROW_NUMBER vs RANK vs DENSE

The RANK, DENSE_RANK and ROW_NUMBER functions are used to return an increasing integer number. All of these three functions require the ORDER BY clause to function in the way intended.

Before explaining how these functions work, let's understand how they can be useful in different scenarios.

Ranking top-selling brands by number of units sold
Ranking top product by revenue
Getting the most populated city in each state

Initial Overview

The behavior and the way in which integers are assigned to each record changes when there are duplicate rows in the resulting table. Here we will have a quick look at what each function returns.

Similarities Between Functions

The RANK, DENSE_RANK and ROW_NUMBER Functions have the following similarities:

The three of them require an ORDER BY clause.
All of them return an increasing integer number with a base value of 1.
When combined with a PARTITION BY clause, these functions reset the returned integer value to 1 when the partition ends.
In the case of no duplicated values in the column used by the ORDER BY, these functions return the exact same output.

RANK Function

The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause. For example, if you want to find the name of the employee with the highest salary, you can use RANK Function. Let’s see RANK Function in action:

  SELECT name, salary,
     RANK() OVER(ORDER BY salary DESC) AS 'Rank'
  FROM Employees

An example of what this query could return would be the following:

DENSE_RANK Function

The DENSE_RANK function is similar to RANK function. However, the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.

  SELECT name, salary,
     DENSE_RANK() OVER(ORDER BY salary DESC) AS 'Dense Rank'
  FROM Employees

ROW_NUMBER Function

The ROW_NUMBER function has no relation with ranking. It just simply returns the row number of the sorted records. Even in the case when there are duplicate records in the column used in the ORDER BY, the ROW_NUMBER function will not display duplicate values.

  SELECT name, salary,
     ROW_NUMBER() OVER(ORDER BY salary DESC) AS 'Row Number'
  FROM Employees

Common use for RANK / DENSE_RANK

Both the RANK and DENSE_RANK function can be used in combination with the PARTITION BY clause. In that case, the rank will reset for each new partition generated.

In this case, let's partition our data through he department. To get the rank of salaries dependent on each department.

SELECT name, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS 'Rank'
FROM Employees

Now you can start using these functions in your code, practice them on some data to solidify your knowledge. That's it for this article, hope that you have learned something new!

ROW_NUMBER() vs RANK() vs DENSE_RANK()