May 27, 2014

SQL Server - Row_Number() vs Rank() vs Dense_Rank()

ROW_NUMBER(), RANK() AND DENSE_RANK() are the ranking functions which helps to assign the value for every row. These function are not only useful for assign the value but it also helps in solving complex queries such as getting top row of each value of the column/table then deleting or updating it, deleting or updating duplicate value etc.

ROW_NUMBER() helps to assign new row number for every row, regardless of duplicates within a partition.

RANK() also helps to assign new row number but it assigns for every distinct row, leaving gaps between groups of duplicates within a partition.

DENSE_RANK() is same as a rank() but it assigns new row number for every distinct row, leaving no gaps between groups of duplicates within a partition.

Syntax:

ROW_NUMBER() OVER (ORDER BY CLAUSE)
ROW_NUMBER() OVER (PARTITION BY CLAUSE ORDER BY CLAUSE)

RANK() OVER (ORDER BY CLAUSE)
RANK() OVER (PARTITION BY CLAUSE ORDER BY CLAUSE)

DENSE_RANK() OVER (ORDER BY CLAUSE)
DENSE_RANK() OVER (PARTITION BY CLAUSE ORDER BY CLAUSE)


For Example:

DECLARE @RANKORDER TABLE
      (ID INT,
       FIRSTRANK INT,
       SECONDRANK INT)

INSERT INTO @RANKORDER VALUES (1, 1, 1)
INSERT INTO @RANKORDER VALUES (2, 2, 1)
INSERT INTO @RANKORDER VALUES (3, 2, 2)
INSERT INTO @RANKORDER VALUES (4, 3, 2)
INSERT INTO @RANKORDER VALUES (5, 3, 3)

SELECT *,
ROW_NUMBER() OVER (ORDER BY SECONDRANK DESC) AS ROWNUMBER,
ROW_NUMBER() OVER (PARTITION BY FIRSTRANK ORDER BY SECONDRANK DESC) AS ROWNUMBERPARTITION,
RANK() OVER (ORDER BY SECONDRANK DESC) AS RANK,
RANK() OVER (PARTITION BY FIRSTRANK ORDER BY SECONDRANK DESC) AS RANKPARTITION,
DENSE_RANK() OVER (ORDER BY SECONDRANK DESC) AS DENSERANK,
DENSE_RANK() OVER (PARTITION BY FIRSTRANK ORDER BY SECONDRANK DESC) AS DENSERANKPARTITION
FROM @RANKORDER
ORDER BY SECONDRANK DESC


The above examples shows ROWNUMBER column returns sequential number to each row based on the sorting order where as the ROWNUMBERPARTITION column returns the values based on the partition or grouped by FIRSTRANK.

Similarly, RANK column also returns sequential number to each row but if duplicate value is found then it assigns same rank number and leaves the gaps between the group of duplicates.

DENSERANK returns value same as RANK does but it fills the gaps. 

No comments: