Microsoft® SQL Server® 2012 Bible

257

Chapter 10: Aggregating, Windowing, and Ranking Data

10

Although these two methods are similar to aggregating data, they should be thought of as a

different technique and technology. This is because they work within an independent sort

order separate from the query’s sort order.

Windowing

As previously stated, windowing in a T-SQL query creates a new perspective of the data.

Even though windowing and ranking are placed together in the T-SQL syntax, the window

must be established prior to the ranking functions.

The OVER() Clause

The OVER() clause creates a new window on the data. Consider it as a new perspective, or

independent ordering of the rows, which may or may not be the same as the sort order of

the ORDER BY clause. In a way, the windowing capability creates an alternative fl ow to the

query with its own sort order and ranking functions.

The complete syntax is OVER(ORDER BY columns). The columns may be any available

column or expression, just like the ORDER BY clause; but unlike the ORDER BY clause,

the OVER() clause won’t accept a column ordinal position, for example, 1 , 2. Also, like the

ORDER BY clause, it can be ascending (asc), the default, or descending (desc); and it can

be sorted by multiple columns.

The window’s sort order can take advantage of indexes and can be fast, even if the sort

order is different from the main query’s sort order.

In the following query, the OVER() clause creates a separate view to the data sorted by

ShipDate (ignore the ROW_NUMBER() function for now):

USE AdventureWorks

GO

SELECT

ROW_NUMBER() OVER(Order By ShipDate) AS RowNumber,

PurchaseOrderID,

ShipDate

FROM Purchasing.PurchaseOrderHeader

WHERE EmployeeID = 259

ORDER BY RowNumber

Result (abbreviated; the ShipDate does not include time information, so the results might

vary within a given date):

RowNumber PurchaseOrderID ShipDate

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

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

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

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

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

http://www.it-ebooks.info