DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to sort employees by ROW_NUMBER for each manager?

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

 

LastName FirstName ManagerLastName ManagerFirstName SeqID
Sánchez Ken NULL NULL 1
Tsoflias Lynn Abbas Syed 1
Arifin Zainal Abolrous Hazem 1
Wu Peng Abolrous Hazem 2
Berndt Matthias Ackerman Pilar 1
Bischoff Jimmy Ackerman Pilar 2
Eaton Susan Ackerman Pilar 3
Kuppa Vamsi Ackerman Pilar 4
Ralls Kim Ackerman Pilar 5
Pak Jae Alberts Amy 1
Valdez Rachel Alberts Amy 2
Varkey Chudukatil Ranjit Alberts Amy 3

 

*/

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

-- 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

 

SalesStaff Country Sequence Number SalesYTD
Tsoflias, Lynn Australia 1 $1,758,385.93
Saraiva, José Canada 1 $3,189,356.25
Vargas, Garrett Canada 2 $1,764,938.99
Varkey Chudukatil, Ranjit France 1 $3,827,950.24
Valdez, Rachel Germany 1 $2,241,204.04
Pak, Jae United Kingdom 1 $5,015,682.38
Mitchell, Linda United States 1 $5,200,475.23
Blythe, Michael United States 2 $4,557,045.05
Carson, Jillian United States 3 $3,857,163.63
Campbell, David United States 4 $3,587,378.43
Ito, Shu United States 5 $3,018,725.49
Reiter, Tsvi United States 6 $2,811,012.72
Mensa-Annan, Tete United States 7 $1,931,620.18

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE