SR

DENSE_RANK()

Rank without gaps — consecutive numbers even when values tie

Advanced 10 minDENSE_RANKrankingwindow

DENSE_RANK: No Gaps

DENSE_RANK() gives tied values the same rank but never skips numbers. After a tie, the next rank is always the immediately following integer — no gaps in the sequence. Here's the comparison:

ROW_NUMBER — 1, 2, 3, 4 — always unique, no ties allowed. Each row gets a distinct sequential number even when values are identical.
RANK — 1, 2, 2, 4 — ties share a rank, but gaps follow. The gap size equals the number of tied items minus one.
DENSE_RANK — 1, 2, 2, 3 — ties share a rank, no gaps ever. The next rank after any tie is always the next consecutive integer.
Top N distinct levels — WHERE dense_rnk <= 3 gives exactly 3 distinct value tiers, regardless of how many individual items share each tier. Ideal for queries like "top 3 salary bands" or "5 highest price levels."
Knowledge Canvas

How DENSE_RANK Works

Rank without gaps — consecutive numbers

  • Tied values share the same rank (like RANK)
  • Next rank is consecutive — no gaps: 1, 2, 2, 3
  • DENSE_RANK ≤ number of distinct values
  • Best for "top N distinct values" queries

ROW_NUMBER vs RANK vs DENSE_RANK

The complete ranking function comparison

ROW_NUMBER: 1, 2, 3, 4 (no ties)
Always unique, arbitrary tie-break
RANK: 1, 2, 2, 4 (gaps after ties)
Ties share rank, next rank skips
DENSE_RANK: 1, 2, 2, 3 (no gaps)
Ties share rank, next is consecutive
Top N rows → ROW_NUMBER
Top N ranks → RANK or DENSE_RANK

When to Use DENSE_RANK

Ideal scenarios

  • "Top 3 price tiers" (may return many products)
  • "Nth highest value" problems
  • Bucketing by rank without gaps
  • Interview classic: "Find the 2nd highest price"

Interview Classic

The Nth highest price

  • DENSE_RANK() OVER (ORDER BY price DESC) AS rnk
  • Filter WHERE rnk = 2 for 2nd highest
  • Returns ALL products at that price
  • Use ROW_NUMBER if you want exactly one row
SELECT name, price FROM (
  SELECT name, price,
         DENSE_RANK() OVER (ORDER BY price DESC) AS rnk
  FROM products
) WHERE rnk = 2;
Syntax
Syntax Template
1DENSE_RANK() OVER (
2 ORDER BY col DESC
3) AS dense_rnk
DENSE_RANK()Same rank for ties, NO gaps: 1, 2, 2, 3
Top N distinctWHERE dense_rnk <= N gives N distinct levels
Sample Data
products
10 rows
nameprice
Wireless Mouse24.99
Office Desk189
LED Desk Lamp45.5
USB-C Cable12.99
Bluetooth Speaker79
Office Chair95
Coffee Maker89.99
Notebook Set8.5
Wireless Headphones159
Standing Desk425

Worked Example

Show the top 3 distinct price levels among products.

SQL
1WITH priced AS (
2 SELECT
3 name,
4 price,
5 DENSE_RANK() OVER (ORDER BY price DESC) AS price_tier
6 FROM products
7)
8SELECT
9 name,
10 price,
11 price_tier
12FROM priced
13WHERE price_tier <= 3;
DENSE_RANK assigns consecutive ranks without gaps. If two products shared the same price, they would get the same tier, and the next price would still be the next consecutive number. Filtering to <= 3 gives exactly 3 distinct price levels.
Output
3 rows
namepriceprice_tier
Standing Desk4251
Office Desk1892
Wireless Headphones1593

Key Concepts

1DENSE_RANK: same rank for ties, NO skip (1, 2, 2, 3)
2Best for "top N distinct levels" queries
3WHERE dense_rnk <= 3 gives exactly 3 value tiers
4Use when you want consecutive ranks regardless of tie count

Pro Tip

DENSE_RANK is the right choice for "top N distinct levels" — like the 3 highest salary bands, regardless of how many employees share each one.

When to Use

Top N salary tiers, price tier analysis, grading systems with consecutive grades, finding the Nth highest value.

Challenge

Solve the problem below

Find the 2nd highest product price (not the 2nd most expensive product — the 2nd distinct price level). Show the price. Use DENSE_RANK.

Your Query

Previous

RANK()

Next

LAG()