Dense Rank & Rank

Dense Rank

Returns the rank of rows within the partition of a result set, without any gaps in the ranking.


Syntax

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

-- Azure SQL Data Warehouse and Parallel Data Warehouse

DENSE_RANK () OVER ( [ [ partition_by_clause ] order_by_clause ] )


Few important points

There can be more than one row with same rank.
Partition can be skipped, if we want Rank applied over whole result set.


Examples




SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO

Ranking all rows in a result set
SELECT TOP(10) BusinessEntityID, Rate, 
       DENSE_RANK() OVER (ORDER BY Rate DESC) AS RankBySalary
FROM HumanResources.EmployeePayHistory;

Rank


RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.



* Just replace Rank to Dense_Rank and vice-versa.