266
Part II: Building Databases and Working with Data
Result (abbreviated):Product
ID Product SalesCount Percentile SubCat SubPer
------- ------------------------ ----------- ---------- --------------- ------
870 Water Bottle - 30 oz. 4688 100 Bottles and Cages 55.6
712 AWC Logo Cap 3382 100 Caps 100.0
921 Mountain Tire Tube 3095 99 Tires and Tubes 17.7
873 Patch Kit/8 Patches 3354 99 Tires and Tubes 19.2
707 Sport-100 Helmet, Red 3083 98 Helmets 33.6
711 Sport-100 Helmet, Blue 3090 98 Helmets 33.7
708 Sport-100 Helmet, Black 3007 97 Helmets 32.8
922 Road Tire Tube 2376 97 Tires and Tubes 13.6
878 Fender Set - Mountain 2121 96 Fenders 100.0
871 Mountain Bottle Cage 2025 96 Bottles and Cages 24.0
...New T-SQL Features in SQL Server 2012
As mentioned earlier, SQL Server 2012 introduced several new arguments, listed in Table
10-2, that you can use with the OVER() clause. Most of the arguments are dependent upon
one or the other and cannot be independently used. As a result, each argument is discussed
in detail, and then sample queries illustrating their use are provided.TABLE 10 -2 New OVER() Clause ArgumentsArgument Description
ROWS/RANGE Limits the rows within a partition for the current row.
BETWEEN Used with ROWS or RANGE to specify starting and ending bound-
ary points of the window.
CURRENT ROW Specifi es the starting or ending point of the window as the cur-
rent row when used with ROWS or the current value when used
with RANGE.
UNBOUNDED PRECEDING Specifi es that the window starts at the fi rst row of the partition.
Can only be used as a starting point.
UNBOUNDED FOLLOWING Specifi cs that the window ends at the last row of the partition.
Can only be used as an ending point.The following query couples the ROWS and UNBOUNDED PRECEDING arguments to create a
cumulative total of the specifi ed salesperson’s Sales for a particular year:USE AdventureWorks
GO
SELECT
sp.FirstName,
sp.LastName,
Year(soh.OrderDate) OrderYear,c10.indd 266c10.indd 266 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM
http://www.it-ebooks.info