Section.12.145. difference-between-rank-dense-rank-and-row-number

Rank, Dense Rank, and Row Number are window functions in SQL used to assign a unique number to each row within a partition, but they differ in their approach and behavior.

Here is a brief explanation of the differences between these three functions:

1. ROW_NUMBER(): This function assigns a unique sequential number to each row within a partition, regardless of any ties. In other words, it will always generate a unique number for each row, with no gaps in the numbering sequence.
2. RANK(): This function assigns a unique rank to each row within a partition based on the ordering of a specified column. If multiple rows have the same value, they will receive the same rank, and the next unique rank will skip the number of rows with ties. For example, if two rows have a rank of 2, the next unique rank will be 4.
3. DENSE_RANK(): This function assigns a unique rank to each row within a partition based on the ordering of a specified column. If multiple rows have the same value, they will receive the same rank, but the next unique rank will be the next integer after the last rank, without skipping any numbers. For example, if two rows have a rank of 2, the next unique rank will be 3.

In summary, ROW_NUMBER() generates a unique number for each row, while RANK() and DENSE_RANK() generate a unique rank based on the ordering of a specified column, with RANK() potentially skipping numbers in case of ties, and DENSE_RANK() not skipping any numbers. The choice between these functions will depend on the specific requirements of the analysis or query.