A SQL statement to handle the database paging

Antshome (original) Starting: CSDN

A statement to handle the database paging

Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort fields

10 = number of records per page

20 = (Current page + 1) * Number of records per page

The above statement can implement pagination, but the last sorted result is ascending, and if you want the result set to be in descending order (such as time), there are two ways to handle

1. Use the following statement, but the efficiency may be reduced by some

SELECT * FROM table name B, (select Top 10 primary key field, sort field from (select Top 20 primary key field, sort field from table name order by sort field desc) A order by sort field) C where B. primary key field = C. primary key field order by C. Sort Fields desc

2. In ADO, move the recordset cursor to the last, then move forward

"The following is an ASP example

Set rstemp = Server.CreateObject ("Adodb.recordset")

Rstemp.open statement, conn,1,1

Rstemp.movelast

For i = 1 to Rstemp.recordcount

' Take value ....

Rstemp.moveprevious

Next

Tested, the above paging method is faster than using temporary table paging, and is easy to use