How to implement SELECT Top n----Limit in MySQL

Accustomed to access MSSQL server friends, may be used in MySQL query the first n records, the habit of using the SELECT top N form of statements, here to explain that MySQL does not have this syntax, MySQL with limit to achieve the relevant functions, and more powerful, Good. The following is an explanation of how limit is used in MySQL:

Grammar:

1 SELECT * FROM table LIMIT [offset,] rows | Rows Offset Offset

The LIMIT clause can be used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. parameter must be an integer constant.
Given two parameters, the first parameter specifies the offset of the first return record row, and the second parameter specifies the maximum number of rows to return records.
The initial record line offset is 0 (not 1): MySQL also supports syntax: LIMIT # offset # In order to be compatible with PostgreSQL.

1 -- Example:23Selectfrom5; --Return to the first 5 rows 45Selectfrom0,5; - -Ibid., return the first 5 lines 67Selectfrom5,ten; --Return 6-15 rows

How to optimize limit

When a query statement offset is very large, such as SELECT * from table limit 10000,10, it is best not to use limit directly, but first obtain the ID of offset, and then directly use the limit size to get the data. The effect will be much better.

Such as:

SELECT * FROM Customers Where customer_id >= (select customer_id from Customers Order by customer_id limit 10000,1) Limi T 10;

MS SQL Server TOP clause

Grammar:

SELECT TOP number|percent column_name (s) from table_name;

Example:

1. Select the first 2 records in the table

Select top 2 * from table;

2. Select 50% of the records in the table

Select top persent from table;

3. Retrieving 第10-20条 records from the database

Select Top *from testtablewhere (id not in (SELECT top idfrom Testtableorder by id)) the ORDER by ID;

Oracle Paging

Grammar:

SELECT column_name (s) from table_name WHERE ROWNUM <= number

Example:

1. Select the first 5 records

SELECT * from Persons WHERE ROWNUM <= 5

Paging:

--Start retrieving N records from the database table in the M record select * FROM (select ROWNUM r,t1.* from table name T1 where ROWNUM < M + N) t2where T2.R >= m--such as: From Table sy The S_option (primary key is SYS_ID) retrieves 20 records starting from the 10th record, with the following statement select * FROM (select ROWNUM r,t1.* from sys_option where ROWNUM <) t2wher E T2. R >= 10

How to implement SELECT Top n----Limit in MySQL