Simple paging script for a single SQL table (continued)

The last time we talked about simple paging, we used not in.

Next we will use Max or min

There are also row_number () system functions above SQL Server 2005

 

Select   *   From Xk_friendlink
-- Retrieve the largest ID
Select   Max (ID) From ( Select   Top   2 ID From Xk_friendlink Order   By ID ASC ) As T
-- Ascending Order
Select   Top   2   *   From Xk_friendlink Where ID > ( Select   Max (ID) From ( Select   Top   2 ID From Xk_friendlink Order   By ID ASC ) As T) Order   By ID ASC

-- Row_number () Auto-increment, non-repetition, no breakpoint in the middle, continuous
Select   * , Row_number () Over ( Order   By ID Desc ) As Rownumber From Xk_friendlink
Select Row_number () Over ( Order   By ID Desc ) As Rownumber From Xk_friendlink

-- Pagesize = 3, pageindex = 2, between pagesize * (PageIndex-1) + 1 and pagesize * pageindex
Declare   @ Pagesize   Int
Set   @ Pagesize = 3
Declare   @ Pageindex   Int
Set   @ Pageindex = 2
Declare   @ Startrow   Int
Set   @ Startrow = @ Pagesize * ( @ Pageindex - 1 ) + 1
Declare   @ Endrow   Int
Set   @ Endrow = @ Pagesize * @ Pageindex
Print   @ Startrow
Print   @ Endrow

Select   *   From ( Select   * , Row_number () Over ( Order   By ID Desc ) As Rownumber From Xk_friendlink)
As TMP Where Rownumber Between   @ Startrow   And   @ Endrow