Microsoft® SQL Server® 2012 Bible

260

Part II: Building Databases and Working with Data

WHERE EmployeeID = 259 AND ROW_NUMBER() OVER(ORDER BY

PurchaseOrderID,

ShipDate)

BETWEEN 21 AND 40

ORDER BY RowNumber

Result:

Msg 4108, Level 15, State 1, Line 4

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Because the WHERE clause occurs early in the query processing — often in the query operation

that actually reads the data from the data source — and the OVER() clause occurs late in the

query processing, the WHERE clause doesn’t yet know about the windowed sort of the data or

the ranking function. The WHERE clause can’t possibly fi lter by the generated row number.

There is a simple solution: Embed the windowing and ranking functionality in a subquery

or common table expression:

USE AdventureWorks

GO

WITH Results

AS

(

SELECT

ROW_NUMBER() OVER(ORDER BY PurchaseOrderID, ShipDate)

AS RowNumber,

PurchaseOrderID,

ShipDate

FROM Purchasing.PurchaseOrderHeader

)

SELECT *

FROM Results

WHERE RowNumber BETWEEN 21 AND 40

Result:

RowNumber PurchaseOrderID ShipDate

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

21 21 2006-01-24 00:00:00.000

22 22 2006-01-24 00:00:00.000

23 23 2006-01-24 00:00:00.000

24 24 2006-01-24 00:00:00.000

25 25 2006-01-24 00:00:00.000

26 26 2006-01-24 00:00:00.000

27 27 2006-01-24 00:00:00.000

28 28 2006-01-24 00:00:00.000

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

30 30 2006-02-17 00:00:00.000

31 31 2006-02-17 00:00:00.000

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

http://www.it-ebooks.info