SQL LIMIT, TOP and FETCH FIRST

SQL provides various ways to limit the number of rows returned by a SELECT statement. Three common ways to limit the number of rows are:

  1. LIMIT (for MySQL and PostgreSQL)

The LIMIT clause is used to limit the number of rows returned by a SELECT statement. The syntax for LIMIT is as follows:

refer t‮i:o‬giftidea.com
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

For example, to retrieve the top 10 rows from a table named "employees", we can use the following query:

SELECT *
FROM employees
LIMIT 10;
  1. TOP (for SQL Server and MS Access)

The TOP clause is used to limit the number of rows returned by a SELECT statement in SQL Server and MS Access. The syntax for TOP is as follows:

SELECT TOP number_of_rows column1, column2, ...
FROM table_name;

For example, to retrieve the top 10 rows from a table named "employees" in SQL Server, we can use the following query:

SELECT TOP 10 *
FROM employees;
  1. FETCH FIRST (for Oracle, IBM DB2, and PostgreSQL)

The FETCH FIRST clause is used to limit the number of rows returned by a SELECT statement in Oracle, IBM DB2, and PostgreSQL. The syntax for FETCH FIRST is as follows:

SELECT column1, column2, ...
FROM table_name
FETCH FIRST number_of_rows ROWS ONLY;

For example, to retrieve the top 10 rows from a table named "employees" in Oracle, we can use the following query:

SELECT *
FROM employees
FETCH FIRST 10 ROWS ONLY;

Note that the syntax and behavior of these clauses may vary slightly between different database systems. It's important to consult the documentation for your specific database system to determine the appropriate way to limit the number of rows returned by a SELECT statement.