Articles Cookbook
Back to list
Standard SQL

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

Database:

Operators:

RANK, DENSE_RANK , OVER, PARTITION BY, ORDER BY, ASC, DESC

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.

idfirst_namelast_namemonthsold products
1LisaBlack 52300
2MaryJacobs52400
3LisaBlack 62700
4MaryJacobs62700
5AlexSmith 62900
6MaryJacobs71200
7LisaBlack 71200
8AlexSmith 71000

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:

rdrfirst_namelast_namemonthsold products
11AlexSmith 62900
22LisaBlack 62700
22MaryJacobs62700
43MaryJacobs52400
54LisaBlack 52300
65MaryJacobs71200
65LisaBlack 71200
86AlexSmith 71000

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:

rdrfirst_namelast_namemonthsold products
11MaryJacobs52400
22LisaBlack 52300
11AlexSmith 62900
22LisaBlack 62700
22MaryJacobs62700
11MaryJacobs71200
11LisaBlack 71200
32AlexSmith 71000

Recommended courses:

Recommended articles:

See also:

go to top