DENSE_RANK

Overview

DENSE_RANK() assigns ranks to rows within a partition without leaving any gaps in the ranking sequence when ties occur.

Learning Objectives

• Calculate continuous ranks using DENSE_RANK().
• Handle tied values without skipping subsequent rank numbers.
• Solve "Nth Highest Value" interview queries easily.

Detailed Concept Explanation

Unlike RANK(), DENSE_RANK() ensures that rank numbers are consecutive without gaps:

• If two rows tie for 1st place, both get DENSE_RANK = 1.
• The next row gets DENSE_RANK = 2.

Code Examples

SQL

-- Find the 2nd highest salary in the company (handling tied salaries cleanly)
WITH RankedSalaries AS (
    SELECT 
        name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS drk
    FROM employees
)
SELECT DISTINCT salary 
FROM RankedSalaries 
WHERE drk = 2;

Best Practices

• Use DENSE_RANK for Nth Highest Queries: When asked to find the "Nth highest salary", always use DENSE_RANK(). ROW_NUMBER() might miss tied salaries, and RANK() might skip rank N if there are ties above it.

Interview Perspective

WITH R AS (SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) rk FROM emp)
SELECT DISTINCT salary FROM R WHERE rk = N;

Interactive Challenges

Summary

DENSE_RANK() assigns consecutive rank numbers to tied values without leaving gaps in the sequence.