Steps for implementing top N in Oracle

Are you very worried about the actual operations to achieve top N in Oracle? If this is the case, the following article will give you the corresponding solution. The following article mainly introduces the solution for achieving top N in Oracle. The following is a detailed description of the relevant content.

1. implement select top n in Oracle

Since Oracle does not support the select top statement, order by and ROWNUM are often used in Oracle to query the select top n statement.

To put it simply, the Oracle 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 of Oracle implementation, we can consider starting with ROWNUM to extract the M records of 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 row num of the record when we get the top n records, 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 th

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 th

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 th

01 first

04 forth

Based on this, we can create a stored procedure with the parameters of the number of records and the number of records to be extracted, so that Oracle can easily extract data by page.