≡ Menu




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 DimProduct.ProductKey) AS RowNum,
 DimProduct.ProductKey, 
           DimProduct.EnglishProductName as Product,
           DimProductSubcategory.ProductSubcategoryKey as SubCategoryKey, 
           DimProductSubcategory.EnglishProductSubcategoryName as SubCategory,
           DimProductCategory.ProductCategoryKey as CategoryKey,
           DimProductCategory.EnglishProductCategoryName  as Category 
FROM         DimProduct INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
                   )   
                      SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
      AND @PageNum * @PageSize
ORDER BY ProductKey;

Comments on this entry are closed.