How do I retrieve some records in the middle of a table?

Yesterday I encountered a problem: how to retrieve some records in the middle of the SQL Server/access data table, for example, to retrieve 9th to 16th "latest" records"ArticleRecord, carefully considered to get a method, the followingCodeThe following table structure is used as an example:

	Articles table	SQL Server Type	Oracle type
PK	ID	INT (auto-increment)	Number (9) (add 1 to the current maximum value during insertion)
	Author	Nvarchar (10)	Nvarchar2 (10)
	Title	Nvarchar (50)	Nvarchar2 (50)
	Pubtime	Datetime	Date

This method is a patchwork SQL statement: Use two top commands to obtain the data we want, for example:

1 Select   *   From  

2 (

3 Select   Top 8  *   From  

4 (

5 Select   Top   16 *  

6 From Articles

7 Order   By Pubtime Desc  

8 )

9 Order   By Pubtime ASC  

10 )

11 Order   By Pubtime Desc  

This idea is "getting the head and the end". To retrieve the 8 records in descending chronological order of 9th to 16th, first retrieve the first 16 records in descending chronological order, since the last 8 records of these 16 records are exactly what we need, we will sort these 16 records in ascending chronological order and retrieve the first 8 records, these 8 records are exactly the records we need, but they are not sorted correctly. Finally, we sort these 8 records in descending chronological order to get the required records and sorting methods.

In fact, there are still many ways to retrieve the intermediate data, which is similar to the paging principle. On the internet, we also find that the stored procedure is used. This method is implemented using ASP. net Forum: creates a temporary table in the stored procedure. The temporary table contains an Sn field (, 3 ,....) and the table's primary key (other fields that can uniquely identify a row of records are also possible) field. The stored procedure may be as follows:

1 Create   Procedure Getallarticles_paged

2 (

3 @ Pageindex   Int ,

4 @ Pagesize   Int ,

5 @ Totalrecords Out Int ,

6 @ Totalpages Out Int

7 )

8 As

9
10 Declare   @ Pagelowerbound   Int

11 Declare   @ Pageupperbound   Int

12
13 -- Set the page Bounds

14 Set   @ Pagelowerbound   =   @ Pagesize   *   @ Pageindex

15 Set   @ Pageupperbound   =   @ Pagelowerbound   +   @ Pagesize   +   1

16
17 -- Create a temp table to store the select results

18 Create   Table # TMP

19 (

20 Recno Int   Identity ( 1 , 1 ) Not   Null ,

21 ArticleID Int

22 )

23
24 Insert   Into # TMP

25 Select   [ ID ]

26 From Articles

27 Order   By Pubtime Desc

28
29 Select A. *

30 From Articles A (nolock), # TMP t

31 Where A. ID = T. ArticleID And

32 T. recno >   @ Pagelowerbound   And

33 T. recno <   @ Pageupperbound

34 Order   By T. recno

35
36 Go  

For Oracle databases, there are several differences that seriously impede the implementation of the above methods. For example, Oracle does not support the top Keyword: however, this does not seem very serious, because it provides the rownum implicit cursor, it can implement features similar to top, such:

Select Top 10From Where

To write

SelectFromWhereAndRownum<= 10 

Rownum is the record number (1, 2, 3 ...), however, there is one troublesome thing: if the SQL statement contains order... in sorting, rownum is actually sorted by "Number" first! In this way, this serial number is not applicable if it is not processed.

As for temporary tables, the temporary tables in Oracle are very different from those in SQL Server,

1 Select   *   From  

2 (

3 Select A. * , Rownum R

4 From  

5 (

6 Select   *  

7 From Articles

8 Order   By Pubtime Desc  

9 )

10 Where Rownum <= Pageupperbound

11 ) B

12 Where R > Pagelowerbound;