What’s the Difference Between RANK and DENSE_RANK in SQL?

Problem

You want to compare the rankings produced by RANK and DENSE_RANK functions.

Example

Our database has a table named sales_assistant with data in the following columns: id, first_name, last_name, month, and sold_products. We want to display each sales assistant’s first and last name and the number of sold products. We also want to rank them in terms of the number of products sold in descending order.

Solution 1: Using RANK and DENSE_RANK

SELECT 
  RANK() OVER(ORDER BY sold products DESC) AS rank,
  DENSE_RANK() OVER(ORDER BY sold products DESC) AS dense_rank,
  first_name,
  last_name,
  month,
  sold_products
FROM sales_assistant;

This query returns two rankings: one produced by RANK and another by DENSE_RANK. What’s the difference?

RANK skips the number of positions after records with the same rank number. The ranking RANK_DENSE returns position numbers from 1 to 6 because it doesn’t skip records with the same rank number:

Discussion

RANK skips positions after equal rankings. The number of positions skipped depends on how many rows had an identical ranking. For example, Mary and Lisa sold the same number of products and are both ranked as #2. With RANK, the next position is #4. The DENSE_RANK does not skip positions after equal rankings. With DENSE_RANK, the next position after Mary and Lisa is #3.

Observer the syntax of RANK and DENSE_RANK functions: after RANK or DENSE_RANK, we use the OVER() clause, which takes an ORDER BY clause with the name of the column to sort before assigning a ranking.

Solution 2: Ranking within a partition

Both RANK and RANK_DENSE work on partitions of data:

SELECT 
  RANK() OVER(PARTITION BY month ORDER BY sold products DESC) AS rank,
  DENSE_RANK() OVER(PARTITION BY month ORDER BY sold products DESC) AS dense_rank,
  first_name,
  last_name,
  month,
  sold_products
FROM sales_assistant;

You can split records into groups according to a given column (in our example, we partition by the month column). In this situation, records are ranked as part of a partition: