RANK and DENSE_RANK difference in SQL

Introduction

RANK and DENSE_RANK help us order data by giving positions to rows based on values, but they handle ties differently.

When you want to list students by their scores and see their positions.

When you want to rank salespeople by sales and handle ties in ranking.

When you want to assign positions to players in a game leaderboard.

When you want to compare products by price and show their rank order.

Syntax

SQL

RANK() OVER (ORDER BY column_name [ASC|DESC])
DENSE_RANK() OVER (ORDER BY column_name [ASC|DESC])

Both functions assign ranks to rows ordered by the specified column.

RANK leaves gaps in ranking numbers when there are ties; DENSE_RANK does not.

Examples

Ranks players by score, skipping rank numbers if scores tie.

SQL

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM players;

Ranks players by score without skipping rank numbers for ties.

SQL

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM players;

Sample Program

This query shows how RANK and DENSE_RANK assign positions to players with tied scores.

SQL

WITH players AS (
  SELECT 'Alice' AS name, 100 AS score UNION ALL
  SELECT 'Bob', 90 UNION ALL
  SELECT 'Charlie', 90 UNION ALL
  SELECT 'David', 80
)
SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players
ORDER BY score DESC;

Important Notes

RANK() can create gaps in ranking numbers after ties.

DENSE_RANK() always gives consecutive ranks without gaps.

Both functions require an ORDER BY clause inside the OVER() clause.

Summary

RANK and DENSE_RANK both assign ranks based on order.

RANK skips numbers after ties; DENSE_RANK does not.

Use them to understand positions when sorting data with possible ties.