datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to page in a double nested list?

Execute the following SQL Server T-SQL script in SSMS Query Editor to create result set paging for Sales Orders and Sales Order Details:

-- T-SQL query with CTE and double ROW_NUMBER sequencing

-- SQL Server paging result set

USE AdventureWorks2008;

 

DECLARE  @PageSize INT,

         @PageNo   INT,

         @FirstRow INT,

         @LastRow  INT

 

SET @PageSize = 40

 

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;

 

RowNo RND SOID SalesOrderDetailID ProductID Qty UP UPD LineTotal
3961 1 73460 117357 880 1 54.99 0 54.99
3962 2 73460 117356 875 1 8.99 0 8.99
3963 3 73460 117355 923 1 4.99 0 4.99
3964 1 73459 117354 707 1 34.99 0 34.99
3965 2 73459 117353 922 1 3.99 0 3.99
3966 1 73458 117352 922 1 3.99 0 3.99
3967 2 73458 117351 931 1 21.49 0 21.49
3968 1 73457 117350 858 1 24.49 0 24.49
3969 2 73457 117349 932 1 24.99 0 24.99
3970 3 73457 117348 922 1 3.99 0 3.99
3971 1 73456 117347 931 1 21.49 0 21.49
3972 1 73455 117346 873 1 2.29 0 2.29
3973 2 73455 117345 921 1 4.99 0 4.99
3974 3 73455 117344 928 1 24.99 0 24.99
3975 1 73454 117343 882 1 53.99 0 53.99
3976 1 73453 117342 708 1 34.99 0 34.99
3977 2 73453 117341 922 1 3.99 0 3.99
3978 3 73453 117340 932 1 24.99 0 24.99
3979 1 73452 117339 878 1 21.98 0 21.98
3980 1 73451 117338 874 1 8.99 0 8.99
3981 2 73451 117337 878 1 21.98 0 21.98
3982 1 73450 117336 711 1 34.99 0 34.99
3983 2 73450 117335 921 1 4.99 0 4.99
3984 1 73449 117334 876 1 120 0 120
3985 2 73449 117333 921 1 4.99 0 4.99
3986 1 73448 117332 875 1 8.99 0 8.99
3987 2 73448 117331 714 1 49.99 0 49.99
3988 3 73448 117330 878 1 21.98 0 21.98
3989 1 73447 117329 870 1 4.99 0 4.99
3990 1 73446 117328 715 1 49.99 0 49.99
3991 2 73446 117327 867 1 69.99 0 69.99
3992 1 73445 117326 708 1 34.99 0 34.99
3993 2 73445 117325 870 1 4.99 0 4.99
3994 1 73444 117324 868 1 69.99 0 69.99
3995 1 73443 117323 865 1 63.5 0 63.5
3996 2 73443 117322 870 1 4.99 0 4.99
3997 3 73443 117321 871 1 9.99 0 9.99
3998 1 73442 117320 871 1 9.99 0 9.99
3999 2 73442 117319 870 1 4.99 0 4.99
4000 1 73441 117318 858 1 24.49 0 24.49

Related article:

SQL Server 2005 Paging Results

 

Exam Prep 70-461
Exam 70-461