This function returns the rank of
each row within a result set partition, with no gaps in the ranking values. The
rank of a specific row is one plus the number of distinct rank values that come
before that specific row. The RANK and DENSE_RANK functions are quite
different, however, since instead of just assigning sequential numbers, they
actually rank the rows based on the ORDER BY expression
There is also a difference
between RANK and DENSE_RANK. The RANK function returns the rank of the current
row compared to all the rows of the partition. The DENSE_RANK function returns
the unique rank of the current row in the partition. RANK says how many rows
before the current one, and DENSE_RANK says how many different values come
before the current value. Another way to think about this is that DENSE_RANK
doesn’t waste any numbers, while RANK skips numbers.
Syntax
|
DENSE_RANK() OVER([PARTITION BY
<col1>[,<col2>,...<n>]]
ORDER BY
<col3>[,<col4>])
|
See the example
Without partition by clause
|
SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd')
AS OrderDate, SalesOrderID,
Dense_RANK() OVER(ORDER BY CustomerID)
AS Rank_Number
FROM Sales.SalesOrderHeader;
|
See the output
With partition by clause
|
SELECT CustomerID, FORMAT(OrderDate,'yyyy-MM-dd')
AS OrderDate, SalesOrderID,
Dense_RANK() OVER(PARTITION BY CustomerID ORDER BY SalesOrderID)
AS Rank_Number
FROM Sales.SalesOrderHeader;
|
See the output
