Solution to limit problems in Oracle Top N and Oracle

Oracle Top N

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
After n data, we can consider starting 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
When the Top N records are obtained, the rownum of the records is extracted at the same time, and then the records numbered m are extracted from the N records, even if the expected results are obtained.

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

3 is only about extracting a record. When we need to extract multiple records, the value of N in 2 should be n> = (m + X-
1) in this range, it is time to make the most economical value equal. 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 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 we can easily extract data by page.

Solution to limit in Oracle

 

Migrating a program from MySQL to Oracle encountered the limit problem in the SQL statement.
The network query method is extremely troublesome and is not suitable for common use.
The following is my solution, which is comparable to limit.

For example, if two to six records are queried from a mobileuser table, the records are sorted by the first time they are used.

MySQL statement:

Select userid, password, firstusetime from mobileuser order by firstusetime DESC limit 2, 6;

The Oracle statement is:

Select
* From (select userid, password, firstusetime, rank () over (order
Firstusetime DESC) Rn from mobileuser) Where rn between 2 and 6;

The rank () over function indicates the ranking basis. The entire SQL statement is to find the data with two to six places in the ranking according to the "Order by firstusetime DESC" standard.

However, there is a problem. According to the ranking, some data is tied in parallel, so that the number of returned data records will exceed our expectation. Then we can add a rownum limit.

Select
* From (select userid, password, firstusetime, rank () over (order
Firstusetime DESC) Rn from mobileuser) Where rn between 2 and 6 and
Rownum <= 5;