How to Replace MySQL\'s LIMIT Keyword with ANSI SQL Equivalents?

ANSI SQL Equivalents for the MySQL LIMIT Keyword

The MySQL LIMIT keyword is used to restrict the number of rows retrieved by a SELECT statement. While it is widely used in MySQL, it is not part of the ANSI SQL standard. This article provides ANSI SQL alternatives to the LIMIT keyword, enabling you to achieve similar functionality across different database systems.

1. DB2:

SELECT * FROM table FETCH FIRST 10 ROWS ONLY;

2. Informix:

SELECT FIRST 10 * FROM table;

3. Microsoft SQL Server and Access:

SELECT TOP 10 * FROM table;

4. Oracle:

SELECT * FROM (SELECT * FROM table) WHERE rownum <= 10;

5. PostgreSQL:

SELECT * FROM table LIMIT 10;

Example:

Consider the MySQL statement:

SELECT * FROM People WHERE Age > 18 LIMIT 2;

which returns the first two rows where Age is greater than 18. The equivalent ANSI SQL statements would be:

• DB2:

  SELECT * FROM People WHERE Age > 18 FETCH FIRST 2 ROWS ONLY;

• Informix:

  SELECT FIRST 2 * FROM People WHERE Age > 18;

• Microsoft SQL Server / Access:

  SELECT TOP 2 * FROM People WHERE Age > 18;

• Oracle:

  SELECT * FROM (SELECT * FROM People WHERE Age > 18) WHERE rownum <= 2;

• PostgreSQL:

  SELECT * FROM People WHERE Age > 18 LIMIT 2;

By using these ANSI SQL alternatives, you can ensure compatibility with different database systems while achieving the same results as with the MySQL LIMIT keyword.

The above is the detailed content of How to Replace MySQL\'s LIMIT Keyword with ANSI SQL Equivalents?. For more information, please follow other related articles on the PHP Chinese website!