How to obtain the first few pieces of data in the Oracle database

How to obtain the first few pieces of data in the Oracle database? It is similar to the SELECTTOPN method in SQL statements. This article will show you the answer, for example!

How to obtain the first few pieces of data in the Oracle database? It is similar to the select top n method in SQL statements. This article will show you the answer, for example!

How to obtain the first few pieces of data in the Oracle database? It is similar to the select top n method in SQL statements. This article will show you the answer, for example!

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 Records extracted)

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 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 NAME

05 th

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

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:

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 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 we can easily extract data by page.