DENSE_RANK() in SQL

Assigns a rank to rows within a result set partition, with no gaps in rank numbers when ties occur.

Syntax

DENSE_RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
)

Return type

BIGINT

DENSE_RANK() Function Example

NULLs ranked together
NULLs ranked last by default

SELECT DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank, 
       product_name, sales_amount 
FROM sales;
-- Output: 1, 'Product A', 1000 
--         1, 'Product B', 1000
--         2, 'Product C', 800

What is DENSE_RANK() in SQL?

The DENSE_RANK() function in SQL assigns a rank to each row within a result set partition, ensuring that tied values receive the same rank but without gaps in the ranking sequence. Unlike RANK(), which skips numbers after ties, DENSE_RANK() assigns the next consecutive rank.

This function is useful for ranking data in competitions, performance metrics, and percentile-based analysis. It is supported in SQL Server, PostgreSQL, Oracle, and MySQL 8.0+ as a window function.

DENSE_RANK() is widely used in grading systems, sales leaderboards, and percentile-based reporting.

Parameters:

PARTITION BY (optional): Columns to partition the rows
ORDER BY (required): Columns that determine the ranking order

Example Use Cases:

-- Student grade rankings

SELECT student_name,
       score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS grade_rank
FROM exam_results;
-- Output:
student_name    score    grade_rank
------------    -----    ----------
Alice Smith     98       1
Bob Johnson     98       1
Carol White     95       2
David Brown     95       2
Emma Davis      92       3
Frank Wilson    90       4
Grace Lee       90       4
Henry Clark     88       5

-- Sales rankings by region

SELECT region,
       salesperson,
       sales_amount,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM sales_data;
-- Output:
region     salesperson     sales_amount    sales_rank
-------    -----------     ------------    ----------
North      John Smith      150000          1
North      Sarah Jones     150000          1
North      Mike Wilson     145000          2
South      Lisa Brown      165000          1
South      David Lee       160000          2
South      Amy Chen        160000          2
West       Tom Clark       155000          1
West       Jane Doe        150000          2
West       Bob Miller      150000          2

-- Product rankings by category

SELECT category,
       product_name,
       price,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY price) AS price_rank
FROM products;
-- Output:
category      product_name    price    price_rank
--------      ------------    -----    ----------
Electronics   Earbuds         29.99    1
Electronics   Mouse           49.99    2
Electronics   Keyboard        89.99    3
Clothing      T-Shirt        19.99    1
Clothing      Jeans          59.99    2
Clothing      Jacket         89.99    3
Books         Paperback      12.99    1
Books         Hardcover      24.99    2
Books         Collector      49.99    3

Notes:

Behavior: Equal values receive same rank, next rank is consecutive
Performance Considerations: May require window function optimization
Version Info: Core window function available in most modern DBMS
Deprecated/Recommended Alternatives: Consider RANK() if gaps desired

Error Handling:

Error: Syntax error if ORDER BY is omitted
Recommendation: Always include ORDER BY clause

Supported Databases:

DBMS

Function / Syntax