RANK() and DENSE_RANK() are both analytical functions used in SQL for ranking rows based on specified criteria. However, there is a difference in how they handle tied or duplicate values. Let's explore each function:
RANK(): The RANK() function assigns a unique rank to each distinct value in the result set. When two or more rows have the same value, they receive the same rank, and the subsequent rank is skipped. The next rank is determined by the number of tied rows plus the skipped ranks. In other words, the ranks may have gaps.
DENSE_RANK(): The DENSE_RANK() function, on the other hand, assigns a unique rank to each distinct value in the result set, similar to RANK(). However, when two or more rows have the same value, they receive the same rank, and the subsequent rank is not skipped. DENSE_RANK() ensures that there are no gaps in the ranking sequence.
To summarize, RANK() assigns a unique rank to each distinct value, with potential gaps in the ranking sequence, while DENSE_RANK() also assigns a unique rank to each distinct value but ensures there are no gaps in the ranking sequence, even for tied values. The choice between these functions depends on the specific requirements of your query or analysis.