Microsoft® SQL Server® 2012 Bible

262

Part II: Building Databases and Working with Data

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.000

The results are the same as the previous query. However, the need for a subquery or com-

mon table expression has been removed. Using OFFSET and FETCH as a paging mechanism

requires running the query only once for each page. In the above query the OFFSET clause

specifi es the number of rows to skip before the results of the query is returned. The FETCH

clause specifi es the number of rows to return after the OFFSET.

RANK() and DENSE_RANK() Functions

The RANK() and DENSE_RANK() functions return values as if the rows were competing

according to the windowed sort order. Any ties are grouped together with the same ranked

value. For example, if Frank and Jim both tied for third place, then they would both receive

a RANK() value of 3.

Using sales data from AdventureWorks, there are ties for least sold products, which makes

it a good table to play with RANK() and DENSE_RANK(). ProductID’s 943 and 911 tie for

third place and ProductID’s 927 and 898 tie for fourth or fi fth place depending on how

ties are counted:

-- Least Sold Products:

USE AdventureWorks

GO

SELECT ProductID, COUNT(*) as [Count]

FROM Sales.SalesOrderDetail

GROUP BY ProductID

ORDER BY COUNT(*);

Result (abbreviated):

ProductID count

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

897 2

942 5

943 6

911 6

927 9

898 9

744 13

903 14

...

Examining the sales data using windowing and the RANK() function returns the ranking

values:

c10.indd 262c10.indd 262 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM

http://www.it-ebooks.info