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

Problem:

You want to compare the rankings produced by RANK and DENSE_RANK and add them as new columns to a table.

Example:

Our database has a table named sales_assistant with data in the following columns: id (primary key), first_name, last_name, month, and sold products.

Let’s display each sales assistant’s first and last name and number of sold products. We also want to rank them in terms of the number of products sold in descending order.

Solution 1:

SELECT RANK() OVER(ORDER BY sold products DESC) AS r,
  DENSE_RANK() OVER(ORDER BY sold products DESC) AS dr,
  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?

Simply put, 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:

If you’d like to rank rows in the result set, SQL offers the RANK() and DENSE_RANK functions. These functions are used in SELECT with others columns. After RANK or DENSE_RANK, we call the OVER() function, which takes an ORDER BY clause with the name of the column to sort before assigning a ranking.

Unlike DENSE_RANK, 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; with DENSE_RANK, the next position is #3.

Both RANK and RANK_DENSE work on partitions of data:

Solution 1:

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

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