Microsoft® SQL Server® 2012 Bible

258

Part II: Building Databases and Working with Data

4 49 2006-03-01 00:00:00.000

5 59 2006-03-05 00:00:00.000

6 69 2006-03-06 00:00:00.000

7 79 2006-03-21 00:00:00.000

SQL Server 2012 introduced several new arguments that you can use with the OVER()

clause. Varying combinations of the new arguments assist T-SQL in limiting the rows with

a partition. The section “New T-SQL Features in SQL Server 2012,” later in this chapter

explains all arguments and illustrates their uses.

Partitioning Within the Window

The OVER() clause normally creates a single sort order, but it can divide the windowed

data into partitions, which are similar to groups in an aggregate GROUP BY query. This is

dramatically powerful because the ranking functions can restart with every partition.

The next query example uses the OVER() clause to create a sort order of the query results by

ShipDate, and then partitions the data by YEAR() and MONTH().The syntax is the opposite

of the logical fl ow. The PARTITION BY goes before the ORDER BY within the OVER() clause:

USE AdventureWorks

GO

SELECT

ROW_NUMBER()

OVER(

PARTITION BY YEAR(ShipDate), Month(ShipDate)

Order By ShipDate

)

AS RowNumber,

PurchaseOrderID,

ShipDate

FROM Purchasing.PurchaseOrderHeader

WHERE EmployeeID = 259

ORDER BY RowNumber

Result (abbreviated):

RowNumber PurchaseOrderID ShipDate

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

1 9 2006-01-23 00:00:00.000

2 19 2006-01-24 00:00:00.000

1 29 2006-02-17 00:00:00.000

1 49 2006-03-01 00:00:00.000

2 59 2006-03-05 00:00:00.000

3 69 2006-03-06 00:00:00.000

4 79 2006-03-21 00:00:00.000

As expected, the windowed sort (in this case, the RowNumber column) restarts with every

new month.

c10.indd 258c10.indd 258 7/30/2012 4:26:13 PM7/30/2012 4:26:13 PM

http://www.it-ebooks.info