How to query data records from N to m in Oracle

1. After testing, the following method is passed:

 

Select * from
(
Select Table name. *, rownum as con from table name where rownum <= m and other query Conditions Order by sorting Conditions
)
Where con> = N;

 

2. Refer to other online methods

 

SQL/Oracle Method for retrieving records from MB to n

 

Use an SQL statement to retrieve the m to N records

Use an SQL statement to retrieve the m to N records

Retrieve records from the table from entry m to entry N: (not in version)

Select Top N-m + 1 *

From table

Where (id not in (select top M-1 ID from Table ))

-- Retrieve the MB to N records from the table (exists Version)

Select Top N-m + 1 * from table as a where not exists

(Select * from (select top m-1 * from Table order by ID) B where B. ID = A. ID)

Order by ID

-- M is the superscript and N is the subscript. For example, extract 8th to 12 records, M = 8, n = 12, and table is the table name.

Select Top N-m + 1 * from table

Where ID> (select max (ID) from

(Select top M-1 ID from Table order by id asc) temp)

Order by ID ASC

#1 floor [main poster] athrun

Directly retrieve paging records in the database

The premise is that the table must have a primary key.

N records after the m record is obtained:

Select Top N *

From [Table]

Where (id not in

(Select top m id

From [Table]

Order by [order])

Order by [order]

Implementation in Oracle, get the m to N records: Select * from

(

Select *, rownum as con from

(

Select * from [Table] Order by [order]

)

Where rownum <= N

)

Where con> = m;

View reply reference

Query several consecutive records in a table

Do not pass the condition parameters of any column to query several consecutive records in the table.

For example, table A and ID columns are primary keys.

ID name sex age

-------------------------

1 luoyi male 21

2 Yaya female 20

3 Lili female 22

4 wuyong male 25

.......................

There are still a lot of records in this table. If I want to take the second and third records, I just want these two records, which will be used not only in programming, in addition, some companies have similar questions during interviews (Haha, I have never met). in Oracle and MSSQLServer, SQLCodeThey are:

I. Oracle

In Oracle, the top keyword cannot be used, but rownum can be used.

1. (select * from a where rownum <= 4) minus (select * from a where rownum <= 1)

In this way, two or three records are obtained. The minus keyword is used to calculate the difference set of two result sets. in mathematics, for example, two sets can be merged, public, and difference set.

2. Select * from (select * from a where rownum <4) B where B. id not in (select ID from a where rownum <2) can also be implemented. The not in operator is used.

Ii. ms SQL Server

There is no minus in the server, and only the second method similar to Oracle can be used.

Select * from (select top 3 * From A) as B where B. id not in (select top 1 ID from)

3. The result is as follows:

ID name sex age

--------------------------------

2 Yaya female 20

3 Lili female 22

View reply reference

Query N records in the database, and then sort the N records.

When I see this topic, some people will write this sentence,

Select top 10 * From tablename order by createtime

This statement is the opposite of the topic.

Correct answer 1:

Select top 10 * From tablename where ID in (select top 10 ID from tablename order by ID) order by createtime

This statement can be used to locate the first 10 records in the table and sort the records by the createtime time.

The requirement is that the table must have a primary key.

Answer 2

No primary key.

Select *

From (select top 10 *

From titles) mm

Order by pubdate DESC

View reply reference

SQL statements that randomly retrieve several records

SQL Server:

Select top 20 * from Table order by newid ()

Access:

Select top 20 * from Table order by RND (ID)

In RND (ID), the ID is an automatically numbered field, which can be completed using any other value.

For example, use the name field (username)

Select top 20 * from Table order by RND (LEN (username ))

MySQL:

Select * from Table order by rand () Limit 20

View reply reference

Author: Yanek

Email: yanek@126.com

Feature: only one page of data is returned for a single query. Instead of getting all the data.

Note:

Pagesize: number of records displayed per page

Cureentpage: Current page number

Select * from (select top pagesize * cureentpage * From user_table order by id asc) as asystable order by id desc) as bsystable order by ID ASC

Example:

Assume that the database table is as follows:

User_table:

ID: primary key, auto-Increment

Username: character

Password: character

Assume there are 80 records, and 10 records are displayed on each page, with IDs ranging from 1 to 80.

Now the data on the third page is sorted in ascending order by ID: The obtained Record ID should be 21 to 30.

The statement should be:

Select * from (select top 10 * from (select top 30 * From user_table order by id asc) as asystable order by id desc) as bsystable order by ID ASC

The principle is as follows:

First, 30 records (3*10) are retrieved in ascending order of IDS, that is, records with IDs between 1 and 30 (select top 30 * From user_table order by id asc)

Then sort the 30 records in descending order by ID.

Then, extract the first 10 records from the 30 records: The obtained records are: IDs are between 30 and 21. This is the data we need, but it is arranged in descending order and does not meet the requirements.

Finally, the data we need will be obtained after re-sorting. IDs are between and 30.