How does the DENSE_RANK function differ from the RANK function?

The DENSE_RANK function is specifically designed to assign ranks in a way that ensures that no gaps exist in the ranking when there are identical values. When multiple rows have the same value, DENSE_RANK assigns them the same rank, and the next rank in the sequence continues from that point without leaving any gaps.

For example, if two rows are tied for rank 1, both are assigned the rank of 1, and the next distinct value will receive a rank of 2. This helps in maintaining a smooth ranking flow, unlike the RANK function, which would jump to 3 in the same scenario, thus creating a gap.

This difference in behavior concerning sequential ranking after identical values is the key feature that distinguishes DENSE_RANK from RANK. It’s not restricted to numeric values only, nor does it categorize data into partitions; rather, it focuses on how ranks are assigned in relation to identical values.