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, andRANK()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:
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.
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.