Oracle database implementation methods for obtaining the first few data

How do I implement a method to get the first few data in an Oracle database? is a method like Select TOP n in an SQL statement. This article will tell you the answer, for example, Yo!

1. Implement select TOP N in Oracle:

Because Oracle does not support the SELECT top statement, the query for select top n is often implemented in Oracle with the combination of order by and RowNum.
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

Here is an example to illustrate briefly.
Customer table Customer (Id,name) has the following data:
ID NAME
First
Second
Third
Forth, Geneva
Fifth
Sixth
Seventh
Eighth
Ninth
Ten Tenth
Last

The SQL statement for the first three customers is drawn according to the letter of name as follows:

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

The output is:
ID NAME
Eighth
Fifth
First

2. Extract the first m (M <= N) record in the top N record:
After obtaining the top n data, we can consider starting with rownum in order to extract the records of the nth record. We know that RowNum is a hidden sub-segment of the data number in the record table, so you can extract the recorded rownum at the same time as the top n record, and then extract the record numbered m from the N record, which is the result we want to get.

From the above analysis it is easy to get the following SQL statement:

SELECT Column Name 1 ... Column name Nfrom
(
SELECT ROWNUM RECNO, column name 1 ... Column name Nfrom
(SELECT column Name 1 ... Column name Nfrom table name order BY column name 1 ... Column name N)
WHERE ROWNUM <= N (number of extracted records)
ORDER by ROWNUM ASC
)
WHERE RECNO = m (M <= N)

Based on the same data from the table above, the SQL statement that gets the information of the second customer sorted alphabetically by name should read:
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

Fifth

3. Extract the nth record in a recordset sorted in some way:
In the 2 description, when m = n is the result of our title 3rd. In fact, the 2nd practice in the n>m part of the data is basically not used, we just for the sake of convenience and use.
As described above, the SQL statement should be:

SELECT Column Name 1 ... Column name Nfrom
(
SELECT ROWNUM RECNO, column name 1 ... Column name Nfrom
(SELECT column Name 1 ... Column name Nfrom table name order BY column name 1 ... Column name N)
WHERE ROWNUM <= N (number of extracted records)
ORDER by ROWNUM ASC
)
WHERE RECNO = N

So, the SQL statement for the example in 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

The result is:
ID NAME
Fifth

4. Extract the X record from the beginning of the record in the recordset that is sorted in a certain way:
The last point is simply to extract a record of the situation, when we need to extract multiple records, at this time in the 2nd of the value of n 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 resulting SQL statement is:

SELECT Column Name 1 ... Column name Nfrom
(
SELECT ROWNUM RECNO, column name 1 ... Column name Nfrom
(
SELECT Column Name 1 ... Column name Nfrom 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)

As an example of the above data, the SQL statement of the 3 records starting with the second record where the letter of name is drawn is:

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 results are as follows:

ID NAME
Fifth
First
Forth, Geneva

Based on this, and then expand the word, as a stored procedure, the beginning of the number of records and the number of records extracted as parameters, it is easy to achieve page extraction data.

Oracle database implementation methods for obtaining the first few data