Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate adding sequence number within groups by row_number over partition by.

-- SQL sequentially numbering groups - MSSQL sequential numbering within groups

-- Microsoft T-SQL row_number (row number) over partition by order by

-- SQL inner join - left join - left outer join – self join

USE AdventureWorks;

SELECT C.LastName,

       C.FirstName,

       ManagerLastName = CM.LastName,

       ManagerFirstName = CM.FirstName,

       SeqID = ROW_NUMBER()

         OVER(PARTITION BY E.ManagerID ORDER BY C.LastName, C.FirstName)

FROM   HumanResources.Employee AS E

       LEFT JOIN HumanResources.Employee AS EM

         ON EM.EmployeeID = E.ManagerID

       LEFT JOIN Person.Contact AS CM

         ON CM.ContactID = EM.ContactID

       INNER JOIN Person.Contact AS C

         ON C.ContactID = E.ContactID

ORDER BY ManagerLastName, ManagerFirstName, SeqID

GO

/*  Partial results

*/

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

-- SQL row numbering groups with partition by - transact sql row number 

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

SELECT LastName + ', ' + FirstName                                  AS SalesStaff,

       CountryRegionName                                            AS Country,

       ROW_NUMBER()

        OVER(PARTITION BY CountryRegionName ORDER BY SalesYTD DESC) AS 'Sequence Number',

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

FROM   AdventureWorks2008.Sales.vSalesPerson

WHERE  TerritoryName IS NOT NULL

       AND SalesYTD > 0;

GO

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