How to implement select top n in Oracle

This article 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:

Double-click all code

12345

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

The following is a simple example.

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

Double-click all code

123456789101112

ID NAME01 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:

Double-click all code

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

Output result:

Double-click all code

1234	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:

Double-click all code

12345678

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:

1234567

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

The result is:

Double-click all code

12	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:

Double-click all code

1234567891011121314151617

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 = NThe SQL statement in example 2 is:SELECT ID, NAME FROM(SELECT ROWNUM RECNO, ID, NAME FROM(SELECT * FROM CUSTOMER ORDER BY NAME)WHERE ROWNUM <= 2ORDER BY ROWNUM ASC)WHERE RECNO = 2

Result:

Double-click all code

12	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:

Double-click all code

123456789

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:

12345678

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:

Double-click all code

1234	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.