Difference between row_number(), rank() and dense_rank() window functions
All three functions operate according to the ORDER BY clause.
The ROW_NUMBER() function generates a unique ranking for each row, even when there are duplicate values.
The RANK() function works like ROW_NUMBER(), but it assigns the same rank to rows with identical values.
The DENSE_RANK() function is similar to RANK(), but it ensures that there are no gaps in the ranking between groups.
SELECT *, ROW_NUMBER() OVER(ORDER BY Col) AS [ROW_NUMBER],
RANK() OVER(ORDER BY Col) AS [RANK],
DENSE_RANK() OVER(ORDER BY Col) AS [DENSE_RANK]
FROM (VALUES('a'),('a'),('a'),('b'),('c'),('c'),('d'),('e')) AS Tab(Col)
Other References:
