HOW TO: Creare un progressivo re-inizializzato al variare di un altro campo

USE AdventureWorks2012;

GO

SELECT 
     FirstName, 
     LastName, 
     TerritoryName, 
     ROUND(SalesYTD,2,1),     ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0

ORDER BY TerritoryName; 

http://msdn.microsoft.com/it-it/library/ms186734.aspx