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 sequence subsets of results?

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
 

SeqNo OrderID ProductID UnitPrice Quantity Discount
1 10248 11 14 12 0
2 10248 42 9.8 10 0
3 10248 72 34.8 5 0
1 10249 14 18.6 9 0
2 10249 51 42.4 40 0
1 10250 41 7.7 10 0
2 10250 51 42.4 35 0.15
3 10250 65 16.8 15 0.15
1 10251 22 16.8 6 0.05
2 10251 57 15.6 15 0.05
3 10251 65 16.8 20 0
1 10252 20 64.8 40 0.05
2 10252 33 2 25 0.05
3 10252 60 27.2 40 0
1 10253 31 10 20 0
2 10253 39 14.4 42 0
3 10253 49 16 40 0

  

*/
 

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

 

Related link:

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

 

 

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