SQL DENSE_RANK

What is SQL DENSE_RANK?

DENSE_RANK assigns consecutive integer rankings to rows within a partition, leaving no gaps when ties occur.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DENSE_RANK: PostgreSQL, MySQL 8+, SQL Server, Oracle, Snowflake, BigQuery, Redshift, SQLite 3.25+, DB2, Teradata

SQL DENSE_RANK Full Explanation

DENSE_RANK is an ANSI-standard window (analytic) function that returns the rank of each row within a result set partition, with equal values receiving the same rank and the next distinct value receiving the immediately following rank (no gaps). It is evaluated after the WHERE, GROUP BY, and HAVING clauses but before the final ORDER BY. Because it is a pure window function, it does not require a GROUP BY and can be combined with other columns in the SELECT list without collapsing rows. Compared with RANK, which skips numbers after ties, DENSE_RANK produces a dense sequence. The function takes no arguments; the ordering and optional partitioning are defined in the OVER clause. It returns a BIGINT or INTEGER depending on the dialect. Caveats: ordering NULLS may affect rank, and window frame clauses are ignored.

SQL DENSE_RANK Syntax

SELECT
  DENSE_RANK() OVER (
    PARTITION BY partition_expression
    ORDER BY sort_expression [ASC|DESC]
  ) AS dense_rank
FROM table_name;

SQL DENSE_RANK Parameters

Example Queries Using SQL DENSE_RANK

-- Rank salespeople by revenue within each region
SELECT
  region,
  salesperson,
  revenue,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS regional_rank
FROM sales;

-- Identify top 3 products company-wide without gaps
WITH ranked AS (
  SELECT
    product_id,
    product_name,
    total_sales,
    DENSE_RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
  FROM product_totals
)
SELECT *
FROM ranked
WHERE sales_rank <= 3;

Expected Output Using SQL DENSE_RANK

Each row gains a new column (dense_rank, regional_rank, sales_rank, etc
)
Rows with identical sort_expression values share the same rank
The next distinct value gets the next consecutive integer, ensuring no skipped numbers

Use Cases with SQL DENSE_RANK

Produce leaderboards where ties share the same position without leaving blanks
Calculate percentiles or banding that rely on dense ranking
Select the N highest or lowest distinct values inside partitions
Compare performance across categories while preserving ties

Common Mistakes with SQL DENSE_RANK

Forgetting the ORDER BY inside the OVER clause – the function requires it
Expecting gaps like those produced by RANK
Confusing PARTITION BY with GROUP BY; PARTITION BY does not collapse rows
Applying a window frame (ROWS BETWEEN) – it is ignored for DENSE_RANK

First Introduced In

SQL:2003 (OLAP window functions)

Frequently Asked Questions

What does DENSE_RANK return?

It returns an integer rank for each row within its partition. Identical ORDER BY values share the same rank, and subsequent ranks increase by 1 without gaps.

Why would I use DENSE_RANK instead of RANK?

Choose DENSE_RANK when you need consecutive numbering without holes after ties, such as leaderboards or tier assignments.

Is DENSE_RANK deterministic?

Only if the ORDER BY list uniquely identifies each row. If multiple rows share the same ORDER BY values and the tie cannot be broken, rank order among those ties is nondeterministic.

How do I get the top N rows using DENSE_RANK?

Wrap the ranking query in a common table expression or subquery, then filter on the computed rank, e.g., `WHERE dense_rank <= N`. This preserves all tied rows at the cutoff.