RANK and DENSE_RANK

Concepts covered: Ranking

ROW_NUMBER forces a strict sequence onto rows that may not have one. Eve and Grace earn the same 90,000, and a report saying Eve is 4th and Grace is 5th asserts a difference the data does not contain. The ranking functions give tied rows the same number. There are 2, and they differ only in what happens to the number after a tie. RANK leaves a gap, DENSE_RANK does not.

RANK for a row is 1 plus the count of rows that sort strictly ahead of it. Eve and Grace have 3 people ahead, so both get 4; Dave has 5 people ahead, so he gets 6, and no row carries 5. The gap is deliberate: a rank of 6 says that 5 people out-earn Dave. DENSE_RANK for a row is the count of distinct values at or above its own, so Dave's 80,000 is the 5th distinct salary and he gets 5, which says that 4 pay levels sit above him. Both statements are true about the same table; they answer different questions.

SELECT emp_name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, emp_name) AS row_pos,
       RANK() OVER (ORDER BY salary DESC) AS rank_pos,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_pos
FROM employees
ORDER BY salary DESC, emp_name

Give Grace a raise to 95,000 in the table and rerun. She ties Frank at 3, Eve drops to a RANK of 5 and a DENSE_RANK of 4, and the row numbers shift by 1. The 3 columns disagree only around ties and agree exactly on a table with no duplicate salaries, which is why a wrong choice of ranking function hides until the first tie arrives in production.

The 2 animations run the same ranking over the same 6 rows. Watch where the counter lands after Eve and Grace: the first jumps to 6 for Dave, the second continues to 5.

SELECT
  emp_name,
  department,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank_pos
FROM employees

That was RANK, which counts rows ahead. DENSE_RANK counts distinct values instead, and Dave's row is the only one that changes.

SELECT
  emp_name,
  department,
  salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_pos
FROM employees

Ranking inside a partition

Both functions take PARTITION BY the way ROW_NUMBER does. Partitioned on department and still ordered by salary, the ranks restart at 1 in each department, and a tie inside 1 department leaves a gap there and nowhere else. Both functions also need an ordering to mean anything. Without one, every row in the window ties with every other, and all 6 employees are ranked 1.

Picking the function from the question

The 3rd highest salary, as a pay level, is DENSE_RANK() = 3. If 2 people tie for 2nd, RANK jumps to 4 and no row carries 3. The top 3 places, with ties sharing a place, is RANK() <= 3, which is how a podium works. A fixed-length list of exactly 3 rows is ROW_NUMBER() <= 3 with a tiebreaker column.

A query must return the employees who earn the 3rd highest salary in the company, and ties are possible at every level. Which outer-query filter is right?

• WHERE row_pos = 3, with row_pos from ROW_NUMBER()
• WHERE rank_pos = 3, with rank_pos from RANK()
• WHERE dense_pos = 3, with dense_pos from DENSE_RANK()
• ORDER BY salary DESC LIMIT 1 OFFSET 2

About this lesson

This section is part of the Window Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.