What are the differences between Rank, Dense Rank, and Row Number, and when should each be used?

Question

Accepted Answer

Rank assigns a unique rank to each row, Dense Rank assigns a unique rank to each distinct row, and Row Number assigns a unique number to each row.

Rank assigns the same rank to rows with the same value, leaving gaps in the ranking if there are ties.
Dense Rank assigns a unique rank to each distinct row, leaving no gaps in the ranking.
Row Number assigns a unique number to each row, without any regard for the values in the rows.
Rank is used when you want to see the ranking of each row, Dense Rank is used when you want to see the ranking of distinct values, and Row Number is used when you simply want to assign a unique number to each row.

Accenture Data Engineer interview questions & answers