Home > Database > Mysql Tutorial > How to Replace MySQL\'s LIMIT Keyword with ANSI SQL Equivalents?

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

Susan Sarandon
Release: 2024-12-15 06:38:13
Original
654 people have browsed it

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;
Copy after login

2. Informix:

SELECT FIRST 10 * FROM table;
Copy after login

3. Microsoft SQL Server and Access:

SELECT TOP 10 * FROM table;
Copy after login

4. Oracle:

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

5. PostgreSQL:

SELECT * FROM table LIMIT 10;
Copy after login

Example:

Consider the MySQL statement:

SELECT * FROM People WHERE Age > 18 LIMIT 2;
Copy after login
Copy after login

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;
    Copy after login
  • Informix:

    SELECT FIRST 2 * FROM People WHERE Age > 18;
    Copy after login
  • Microsoft SQL Server / Access:

    SELECT TOP 2 * FROM People WHERE Age > 18;
    Copy after login
  • Oracle:

    SELECT * FROM (SELECT * FROM People WHERE Age > 18) WHERE rownum <= 2;
    Copy after login
  • PostgreSQL:

    SELECT * FROM People WHERE Age > 18 LIMIT 2;
    Copy after login
    Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template