What is RANK() and DENSE_RANK()?

In SQL, `RANK()` and `DENSE_RANK()` are powerful window functions used to assign a rank to each row within a partition of a result set. They are particularly useful for scenarios where you need to identify top N records, or order items based on specific criteria, while handling ties in different ways.

Both RANK() and DENSE_RANK() require an ORDER BY clause within their OVER() specification to define the ranking order. Optionally, a PARTITION BY clause can be used to divide the result set into subsets, with the ranking applied independently within each partition.

RANK()

The RANK() function assigns a unique rank to each row within its partition. If two or more rows have the same values for the ordering columns (i.e., they are tied), they receive the same rank. However, RANK() will then skip the subsequent rank numbers, meaning the next unique rank will be incremented by the number of tied rows plus one. For example, if two rows are tied at rank 2, the next row will receive rank 4 (skipping 3).

SELECT
    ProductName,
    Category,
    Price,
    RANK() OVER (PARTITION BY Category ORDER BY Price DESC) AS RankByPrice
FROM
    Products;

DENSE_RANK()

The DENSE_RANK() function also assigns a unique rank to each row, similar to RANK(). The key difference is how it handles ties: while tied rows receive the same rank, DENSE_RANK() does not skip any rank numbers. The rank for the next distinct value will always be exactly one greater than the previous rank, regardless of how many rows were tied. For example, if two rows are tied at rank 2, the next row will receive rank 3 (not skipping 3).

SELECT
    ProductName,
    Category,
    Price,
    DENSE_RANK() OVER (PARTITION BY Category ORDER BY Price DESC) AS DenseRankByPrice
FROM
    Products;

Key Differences and When to Use Each

The fundamental distinction lies in how they handle gaps in ranking after ties. RANK() creates gaps, while DENSE_RANK() does not. This difference dictates their suitability for various scenarios.

Choosing between RANK() and DENSE_RANK() depends entirely on whether you want the ranks to be consecutive or if skipping ranks after ties is acceptable for your analysis.