Specific solution description for implementing top N3 in Oracle

The following articles mainly describe the three solutions for implementing top N in Oracle. This article mainly introduces the content to be explained in the way of related instances. The following describes the specific content. I hope you will have some help in this aspect after browsing.

1. implement select top n in Oracle

Because Oracle databases do not support the select top statement, ORDER BYxian and ROWNUM are often used in Oracle databases to query the select top n statements.

To put it simply, the implementation method is as follows:

 
 

  
  
SELECT column name 1... column name n FROM
  
  
(SELECT column name 1... column name n FROM table name order by column name 1... column name n)
  
  
Where rownum <= N number of extracted Records)
  
  
ORDER BY ROWNUM ASC
 
 

The following is a simple example.

The customer (id, name) Table has the following data:

ID NAME

01 first

02 Second

03 third

04 forth

05 th

06 sixth

07 seventh

08 eighth

09 ninth

10 tenth

11 last

The SQL statements of the first three customers are extracted by NAME as follows:

 
 

  
  
SELECT * FROM   
  
  
(SELECT * FROM CUSTOMER ORDER BY NAME)   
  
  
WHERE ROWNUM <= 3   
  
  
ORDER BY ROWNUM ASC   
 
 

Output result:

ID NAME

08 eighth

05 th

01 first

2. Extract MM <= N) records from the top n records

After obtaining the top n data, we can start with ROWNUM to extract the M record from the N records. We know that ROWNUM is a hidden sub-segment of the Data number in the record table, so we can extract the ROWNUM of the record when we get the top n records in Oracle, then, we extract records numbered M from the N records, even if we want to get the results.

From the above analysis, you can easily obtain the following SQL statement.

 
 

  
  
SELECT column name 1... column name n FROM
  
  
(
  
  
Select rownum recno, column name 1... column name nFROM
  
  
(SELECT column name 1... column name n FROM table name order by column name 1... column name n)
  
  
Where rownum <= N number of extracted Records)
  
  
ORDER BY ROWNUM ASC
  
  
)
  
  
Where recno = MM <= N)
 
 

Based on the data in the preceding table, the SQL statement for obtaining the information of the second customer in alphabetical order of NAME should be written as follows:

 
 

  
  
SELECT ID, NAME FROM   
  
  
(   
  
  
SELECT ROWNUM RECNO, ID, NAME FROM   
  
  
(SELECT * FROM CUSTOMER ORDER BY NAME)   
  
  
WHERE ROWNUM <= 3   
  
  
ORDER BY ROWNUM ASC )   
  
  
WHERE RECNO = 2   
  
  
 
 
 

The result is:

 
 

  
  
ID NAME  
  
  
05 fifth   
 
 

3. Extract the N records from the record set sorted in a certain way

In the description in 2, when M is N, it is the result of our title. In fact, the N> M data in the 2 approach is basically not used. We just use it to illustrate convenience.

As described above, the SQL statement should be:

 
 

  
  
SELECT column name 1... column name n FROM
  
  
(
  
  
Select rownum recno, column name 1... column name nFROM
  
  
(SELECT column name 1... column name n FROM table name order by column name 1... column name n)
  
  
Where rownum <= N number of extracted Records)
  
  
ORDER BY ROWNUM ASC
  
  
)
  
  
Where recno = N
 
 

The SQL statement in example 2 is:

 
 

  
  
SELECT ID, NAME FROM   
  
  
(   
  
  
SELECT ROWNUM RECNO, ID, NAME FROM   
  
  
(SELECT * FROM CUSTOMER ORDER BY NAME)   
  
  
WHERE ROWNUM <= 2   
  
  
ORDER BY ROWNUM ASC   
  
  
)   
  
  
WHERE RECNO = 2   
  
  
 
 
 

Result:

 
 

  
  
ID NAME  
  
  
05 fifth   
 
 

4. Extract the X records starting from the M records in the record set sorted in a certain way

3 is just about extracting a record. When we need to extract multiple records, at this time, the value of N in 2 should be in the range of N> = (M + X-1). It is time to make the most economical value equal to the value. Of course, the final extraction condition is not RECNO = N, it should be recno between m and (M + X-1), so the following SQL statement is:

 
 

  
  
SELECT column name 1... column name n FROM
  
  
(
  
  
Select rownum recno, column name 1... column name nFROM
  
  
(
  
  
SELECT column name 1... column name n FROM table name order by column name 1... column name n)
  
  
Where rownum <= N> = (M + X-1 ))
  
  
ORDER BY ROWNUM ASC
  
  
)
  
  
Where recno between m and (M + X-1)
 
 

Take the preceding data as an example. The SQL statement for extracting the three records starting from the first 2nd records with the NAME letter is as follows:

 
 

  
  
SELECT ID, NAME FROM   
  
  
(   
  
  
SELECT ROWNUM RECNO, ID, NAME FROM   
  
  
(SELECT * FROM CUSTOMER ORDER BY NAME)   
  
  
WHERE ROWNUM <= (2 + 3 - 1)   
  
  
ORDER BY ROWNUM ASC   
  
  
)   
  
  
WHERE RECNO BETWEEN 2 AND (2 + 3 - 1)   
  
  
 
 
 

The result is as follows:

 
 

  
  
ID NAME  
  
  
05 fifth  
  
  
01 first  
  
  
04 forth   
 
 

Based on this, we can create a stored procedure with the following parameters: the number of records and the number of records extracted can be easily extracted by top N pages in Oracle.

Article by: http://www.programbbs.com/doc/class10-2.htm