Sunday, May 2, 2010

Difference between RowNumber, Rank and Dense Rank

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

INSERT T VALUES (0, 1, 6)
INSERT T VALUES (0, 1, 4)
INSERT T VALUES (0, 3, 2)
INSERT T VALUES (0, 3, 0)
INSERT T VALUES (1, 0, 7)
INSERT T VALUES (1, 0, 5)
INSERT T VALUES (0, 2, 3)
INSERT T VALUES (0, 2, 1)

SELECT *,
ROW_NUMBER() OVER (ORDER BY 8) AS RowNumber,
RANK() OVER (ORDER BY 8) AS Rank,
DENSE_RANK() OVER (ORDER BY 8) AS DenseRank
FROM T

PK A B C RowNumber Rank DenseRank
----- ----- ----- ----- ---------- ---------- ----------
5 1 0 7 1 1 1
6 1 0 5 2 1 1
1 0 1 6 3 3 2
2 0 1 4 4 3 2
7 0 2 3 5 5 3
8 0 2 1 6 5 3
3 0 3 2 7 7 4
4 0 3 0 8 7 4

Notice how the ROW_NUMBER function ignores the duplicate values for column B
and assigns the unique integers from 1 to 8 to the 8 rows while the RANK
and DENSE_RANK functions assigns the same value to each of the pairs of duplicate rows.
Moreover, notice how the RANK function counts the duplicate rows even while
it assigns the same value to each duplicate row whereas the DENSE_RANK function
does not count the duplicate rows.

No comments: