Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate sequence number generation for subsets in the result set.

-- SQL row numbering groups with row_number() partition by

-- SQL row number each salesperson within a country with sales descending

SELECT LastName + ', ' + FirstName                                  AS FullName,

       CountryRegionName                                            AS Country,

       ROW_NUMBER()

        OVER(PARTITION BY CountryRegionName ORDER BY SalesYTD DESC) AS SequenceNo,

       '$' + convert(VARCHAR,SalesYTD,1)                            AS SalesYTD

FROM   AdventureWorks2008.Sales.vSalesPerson

WHERE  TerritoryName IS NOT NULL

       AND SalesYTD > 0;

GO

/* FullName       Country           SequenceNo  SalesYTD

Tsoflias, Lynn    Australia         1           $1,421,810.92

Saraiva, José     Canada            1           $2,604,540.72

Vargas, Garrett   Canada            2           $1,453,719.47

Varkey Chudukatil France            1           $3,121,616.32

Valdez, Rachel    Germany           1           $1,827,066.71

Pak, Jae          United Kingdom    1           $4,116,871.23

Mitchell, Linda   United States     1           $4,251,368.55

Blythe, Michael   United States     2           $3,763,178.18

Carson, Jillian   United States     3           $3,189,418.37

Ito, Shu          United States     4           $2,458,535.62

Reiter, Tsvi      United States     5           $2,315,185.61

Mensa-Annan, Tete United States     6           $1,576,562.20

Campbell, David   United States     7           $1,573,012.94

Ansman-Wolfe,     United States     8           $1,352,577.13

*/

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

------------
-- SQL sequence number for subset: same OrderID - SQL self join
------------
USE Northwind
GO
SELECT   SeqNo,
         odet.OrderID,
         odet.ProductID,
         UnitPrice,
         Quantity,
         Discount = convert(NUMERIC(3,2),Discount)
FROM     [Order Details] odet
         JOIN (SELECT   count(* ) SeqNo,
                        a.OrderID,
                        a.ProductID
               FROM     [Order Details] A
                        INNER JOIN [Order Details] B
                          ON A.ProductID >= B.ProductID
                             AND A.OrderID = B.OrderID
               GROUP BY A.OrderID,
                        A.ProductID) a
           ON odet.OrderID = a.OrderID
              AND odet.ProductID = a.ProductID
WHERE    odet.OrderID < 10400
ORDER BY odet.OrderID,
         odet.ProductID,
         SeqNo
GO
/* Partial results
 

*/
 

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

Related link:

http://msdn.microsoft.com/en-us/library/ms186734.aspx