Paging of query records using standard SQL statements

Source: Internet
Author: User
In most applications, it is common to return a large number of qualified records. The most typical method is to search. In the search application, the user gives the search criteria and the server finds the matching records. However, a search usually returns a large amount of data records. If you are on a webpage, the page is usually implemented by pagination. The page provides buttons such as the previous page and next page to implement paging.
The following methods are used to implement paging:
1. Find method of EJB
2. Put all records in the collection object before the first page is displayed, and all subsequent pages are retrieved in the memory.
3. Each page goes to the database for a duplicate query, and the matching records are displayed.
4. Save the search results of the previous pages in the memory (frequently used), and connect the subsequent records (these data are not patiently viewed by most users) to the database.
5. Other cache mechanisms.

We can select a method suitable for ourselves based on the actual situation. I will introduce you to a standard SQL statement to obtain qualified data. For example, from 10th to 20 records that meet the conditions (in the where Statement. In this way, there is a necessary condition for getting a record. There must be a field that can identify the record order, such as ID and time. The following is an example:
Query 10th to 20 records in the t_table table, sorted by ID.
The SQL statement is:
Select * From t_table T1 where (select count (*) from t_table T2 where <> = 10 and (select count (*) from t_table T2 where < <20

Another example is to query the records from key = '000000' 123 to 20 in the t_table table, sorted by ID.
Select * From t_table T1 where (select count (*) from t_table T2 where < and t2.key = '2013')> = 10 and (select count (*) from t_table T2 where < and t2.key = '000000') <20 and t1.key = '000000'

of course, the ID in the condition can be changed to any field that can identify the record sequence.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.