SQLUSA

Microsoft SQL Server 2005 Best Practices

How to page in a double nested list?

 

Execute the following script in Query Editor to create result set paging for Sales Orders and Sales Order Details:

USE AdventureWorks


DECLARE @PageSize INT, @PageNo INT, @FirstRow INT, @LastRow INT

SET @PageSize = 200
SET @PageNo = 100

SET @FirstRow = ( @PageNo - 1) * @PageSize + 1
SET @LastRow = @PageSize + (@PageNo - 1) * @PageSize;

WITH cteSalesOrder AS
(
SELECT h.SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal,
ROW_NUMBER() OVER (ORDER BY h.SalesOrderID DESC) AS RowNo,
ROW_NUMBER() OVER (PARTITION BY h.SalesOrderID
ORDER BY SalesOrderDetailID DESC) AS RowNoDetail
FROM Sales.SalesOrderDetail d
JOIN Sales.SalesOrderHeader h
ON h.SalesOrderID = d.SalesOrderID
)
SELECT RowNo, RowNoDetail, SalesOrderID, SalesOrderDetailID,
ProductID, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal
FROM cteSalesOrder
WHERE RowNo BETWEEN @FirstRow AND @LastRow
ORDER BY RowNo ASC;

 

SQLUSA - The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page