261
Chapter 10: Aggregating, Windowing, and Ranking Data
10
32 32 2006-02-17 00:00:00.000
33 33 2006-02-25 00:00:00.000
34 34 2006-02-25 00:00:00.000
35 35 2006-02-25 00:00:00.000
36 36 2006-02-25 00:00:00.000
37 37 2006-02-25 00:00:00.000
38 38 2006-02-25 00:00:00.000
39 39 2006-02-25 00:00:00.000
40 40 2006-02-25 00:00:00.000The second query in this chapter, in the “Partitioning Within the Window” section, showed
how grouping the sort order of the window generated row numbers that started over with
every new partition.SQL Server 2012 introduces two new arguments, OFFSET and FETCH, to the ORDER BY
clause that further simplifi es paging in T-SQL. Although they are two operators, they must
be used together for paging to correctly work. The following query illustrates their use:USE AdventureWorks
GO
SELECT
ROW_NUMBER() OVER(ORDER BY PurchaseOrderID, ShipDate) AS RowNumber,
PurchaseOrderID,
ShipDate
FROM Purchasing.PurchaseOrderHeader
ORDER BY RowNumber
OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLYResult: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
32 32 2006-02-17 00:00:00.000
33 33 2006-02-25 00:00:00.000
34 34 2006-02-25 00:00:00.000
35 35 2006-02-25 00:00:00.000
36 36 2006-02-25 00:00:00.000c10.indd 261c10.indd 261 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM
http://www.it-ebooks.info