SQL - Rank()

RANK 

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Ranking Rows Within A Partition

ranks the products in inventory the specified inventory locations according to their quantities. The result set is partitioned by LocationID and logically ordered by Quantity. Notice that products 494 and 495 have the same quantity. Because they are tied, they are both ranked one.

USE AdventureWorks2012; 

GO 

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity 

    ,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 

ProductID   Name                   LocationID   Quantity Rank 

----------- ---------------------- ------------ -------- ---- 

494         Paint - Silver         3            49       1 

495         Paint - Blue           3            49       1 

493         Paint - Red            3            41       3 

496         Paint - Yellow         3            30       4 

492         Paint - Black          3            17       5 

495         Paint - Blue           4            35       1 

496         Paint - Yellow         4            25       2 

493         Paint - Red            4            24       3 

492         Paint - Black          4            14       4 

494         Paint - Silver         4            12       5 

 (10 row(s) affected) 

Ranking All Rows In A Result Set

returns the top ten employees ranked by their salary. Because a PARTITION BY clause was not specified, the RANK function was applied to all rows in the result set.

USE AdventureWorks2012 

SELECT TOP(10) BusinessEntityID, Rate,  

       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary 

FROM HumanResources.EmployeePayHistory AS eph1 

WHERE RateChangeDate = (SELECT MAX(RateChangeDate)  

                        FROM HumanResources.EmployeePayHistory AS eph2 

                        WHERE eph1.BusinessEntityID = eph2.BusinessEntityID) 

ORDER BY BusinessEntityID; 

BusinessEntityID Rate                  RankBySalary 

---------------- --------------------- -------------------- 

1                125.50                1 

2                63.4615               4 

3                43.2692               8 

4                29.8462               19 

5                32.6923               16 

6                32.6923               16 

7                50.4808               6 

8                40.8654               10 

9                40.8654               10 

10               42.4808               9 

Ranking Rows Within A Partition

ranks the sales representatives in each sales territory according to their total sales. The rowset is partitioned by SalesTerritoryGroup and sorted by SalesAmountQuota.

-- Uses AdventureWorks 

 

SELECT LastName, SUM(SalesAmountQuota) AS TotalSales, SalesTerritoryRegion, 

    RANK() OVER (PARTITION BY SalesTerritoryRegion ORDER BY SUM(SalesAmountQuota) DESC ) AS RankResult 

FROM dbo.DimEmployee AS e 

INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey 

INNER JOIN dbo.DimSalesTerritory AS st ON e.SalesTerritoryKey = st.SalesTerritoryKey 

WHERE SalesPersonFlag = 1 AND SalesTerritoryRegion != N'NA' 

GROUP BY LastName, SalesTerritoryRegion; 

LastName          TotalSales     SalesTerritoryRegion  RankResult

----------------  -------------  -------------------  --------

Tsoflias          1687000.0000   Australia            1

Saraiva           7098000.0000   Canada               1

Vargas            4365000.0000   Canada               2

Carson            12198000.0000  Central              1

Varkey Chudukatil 5557000.0000   France               1

Valdez            2287000.0000   Germany              1

Blythe            11162000.0000  Northeast            1

Campbell          4025000.0000   Northwest            1

Ansman-Wolfe      3551000.0000   Northwest            2

Mensa-Annan       2753000.0000   Northwest            3

Reiter            8541000.0000   Southeast            1

Mitchell          11786000.0000  Southwest            1

Ito               7804000.0000   Southwest            2

Pak               10514000.0000  United Kingdom       1