Trick to selecting rows using ROW_NUMBER() in SQL2005

Was trying to use the new SQL2005 ROW_NUMBER() function to get “paged” results. There’s a bit of a trick to using it though.  Only way we could get it to work was to wrap inside another sql clause.

E.g we have a table of Names, and we display them in groups of 10.  So to get the 2nd page can do a query such as (of course you’d put this in an sp with params for the page size and number etc)

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [Name]) as RowNumber, [Name]
FROM Names) AS Names
WHERE RowNumber BETWEEN 11 AND 20