Rank Over

,RANK() OVER
    (PARTITION BY col] ORDER BY [colA] desc, [colB] desc) AS Rank

from MSN  (http://msdn.microsoft.com/en-us/library/ms176102.aspx):

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