Microsoft® SQL Server 2008 R2 Unleashed

Using Row Numbers for Paging Results

Typical uses for row numbers are for paging through the results of a query and for selecting a specific subset of rows from within the result set. Essentially, given a page size in terms of number of rows, and a page number, you can return the rows that belong to that given page.

For example, suppose you want to return the second page of rows from a query similar to the one shown in Listing 43.45. Assuming a page size of five rows, the query shown in Listing 43.50 uses a CTE to first calculate the row numbers according to the ranking by number of titles, and then only those rows with numbers 6 through 10, which belong to the second page, are returned.

Listing 43.50 Using ROW_NUMBER to Page Through Results ...