What is the difference between RANK and DENSE_RANK in SQL?

In SQL, RANK and DENSE_RANK are window functions used to assign rankings to rows in a result set, but they handle ties (duplicate values) differently. The key difference is that RANK skips subsequent ranking numbers after a tie, while DENSE_RANK maintains consecutive rankings without gaps. This distinction affects how rankings are calculated and displayed, especially when multiple rows share the same value.

RANK: Skipping Rankings After Ties When using RANK, if two or more rows have the same value (a tie), they receive the same rank. However, the next distinct value is assigned a rank that accounts for the number of tied rows. For example, consider a table of exam scores: if two students tie for first place with a score of 95, both receive RANK = 1. The next highest score (say, 90) skips rank 2 and is assigned RANK = 3. This creates gaps in the ranking sequence. The syntax for RANK is RANK() OVER (ORDER BY column_name), where the ORDER BY clause defines the ranking criteria. This behavior is useful when you need to reflect the position of rows relative to all others, including gaps caused by ties.

DENSE_RANK: No Gaps in Rankings DENSE_RANK also assigns the same rank to tied rows but ensures the next distinct value follows consecutively. Using the same exam scores example, if two students tie at rank 1 with 95, the next score of 90 is assigned DENSE_RANK = 2 (not 3). The rankings remain sequential, avoiding gaps. The syntax mirrors RANK: DENSE_RANK() OVER (ORDER BY column_name). This function is preferable when you want rankings to represent tiers or groups without gaps, such as categorizing employees into “gold,” “silver,” or “bronze” performance tiers where gaps in numbering would be irrelevant.

When to Use Each Choose RANK when you need to reflect the exact positional order, including gaps (e.g., competition standings where skipping ranks matters). Use DENSE_RANK when consecutive rankings are more important than positional accuracy (e.g., assigning priority levels or grouping results into non-gapped tiers). Both functions rely on the ORDER BY clause to determine ranking logic and are often paired with PARTITION BY to segment data into groups. Understanding their behavior helps ensure accurate reporting and analysis in scenarios involving tied values.