T-SQL: How to do SQL Server paging with ROW_NUMBER()?

DECLARE @PageNum AS INT;

DECLARE @PageSize AS INT;

SET @PageNum = 2;

SET @PageSize = 10;

 

WITH OrdersRN AS

(

    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum

          ,OrderID

          ,OrderDate

          ,CustomerID

          ,EmployeeID

      FROM dbo.Orders

)

 

SELECT *

FROM OrdersRN

WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1

      AND @PageNum * @PageSize

ORDER BY OrderDate,OrderID;

 

 


Posted on Thursday, November 22, 2007 by | Comments (1) | Add Comment

Comments

Gravatar

Re: T-SQL: How to do SQL Server paging with ROW_NUMBER()?

No quesiton this is the place to get this info, thanks y'all.

Posted on 11/27/2012 4:37:01 AM by Thu #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5