Procedure for implementing top N in Oracle

The following articles mainly introduce the actual operation steps for implementing top N in Oracle. We all know that the select top statement is not supported by Oracle, therefore, we often use the combination of order by and ROWNUM in Oracle to top N to implement the select top n query.

To put it simply, the Oracle top N 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   
  
  
first  
  
  
Second  
  
  
third  
  
  
forth  
  
  
fifth  
  
  
sixth  
  
  
seventh  
  
  
eighth  
  
  
ninth  
  
  
tenth  
  
  
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  
  
  
eighth  
  
  
fifth  
  
  
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 storage process with the extension. We can easily extract data from top N pages by using Oracle by setting the number of records and the number of records extracted.

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