How to implement select top n in ORACLE

This document uses examples to teach you how to implement the select top n method in Oracle. 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 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 first02 Second03 third04 forth05 fifth06 sixth07 seventh08 eighth09 ninth10 tenth11 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 NAME08 eighth05 fifth01 first 2. Extract the nth M (M <= N) record from the top n record: 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 row num of the record when we get the top n records, then we can extract records numbered M from these N records, which is the expected result. 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 Records extracted) order by rownum asc) where recno = M (M <= 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 NAME05 fifth 3. Extract the N records from the record set sorted in a certain way: In the description of 2, when M = N, it is the result of the third point in our title. In fact, the second approach is basically not used in the N> M data. 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 Records extracted) order by rownum asc) where recno = N, then 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 NAME05 fifth 4. Extract the X records starting from the M records in the record set sorted in a certain way: The above is just about extracting a record. When we need to extract multiple records, at this time, the value of N in the second point should be in the range of N> = (M + X-1). Of course, the most economical value is the time to take the equal sign. 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 (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 second record 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 NAME05 fifth01 first04 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 we can easily extract data by page.