intermediate

DENSE_RANK

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

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

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

Note

Interview Question: How do you find the Nth highest salary in SQL?

Use DENSE_RANK() inside a CTE:

sql
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

Challenge 1: Dense Rank Employees

Select 'name', 'salary', and DENSE_RANK() OVER (ORDER BY salary DESC) AS drk from 'employees'.


Summary

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

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.