O'Reilly logo

Microsoft® SQL Server 2008 R2 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required