SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause
SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause
SQL TOP, LIMIT, FETCH FIRST, and ROWNUM clauses are used to limit the number of rows returned by a query. These clauses are often used when dealing with large tables to improve query performance or to display a subset of data to the user.
Here are examples of how to use these clauses:
- SQL TOP Clause:
The TOP clause is used in SQL Server and Azure SQL Database to limit the number of rows returned by a SELECT statement. The syntax is as follows:
SELECT TOP n column1, column2, ...
FROM table_name
WHERE condition;
Here, n is the number of rows to be returned. column1, column2, etc. are the names of the columns to be returned. table_name is the name of the table from which the data is to be retrieved. The WHERE clause is used to specify the condition that must be met for the rows to be returned.
Example:
SELECT TOP 10 *
FROM customers
ORDER BY last_name;
In this example, the query returns the first 10 rows of the customers table sorted by last name.
- SQL LIMIT Clause:
The LIMIT clause is used in MySQL and PostgreSQL to limit the number of rows returned by a SELECT statement. The syntax is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition
LIMIT n;
Here, n is the number of rows to be returned. column1, column2, etc. are the names of the columns to be returned. table_name is the name of the table from which the data is to be retrieved. The WHERE clause is used to specify the condition that must be met for the rows to be returned.
Example:
SELECT *
FROM orders
WHERE status = 'completed'
ORDER BY order_date DESC
LIMIT 5;
In this example, the query returns the first 5 completed orders sorted by order date in descending order.
- SQL FETCH FIRST Clause:
The FETCH FIRST clause is used in IBM Db2 and Oracle to limit the number of rows returned by a SELECT statement. The syntax is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition
FETCH FIRST n ROWS ONLY;
Here, n is the number of rows to be returned. column1, column2, etc. are the names of the columns to be returned. table_name is the name of the table from which the data is to be retrieved. The WHERE clause is used to specify the condition that must be met for the rows to be returned.
Example:
SELECT *
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;
In this example, the query returns the top 3 most expensive products.
- SQL ROWNUM Clause:
The ROWNUM clause is used in Oracle to limit the number of rows returned by a SELECT statement. The syntax is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= n;
Here, n is the number of rows to be returned. column1, column2, etc. are the names of the columns to be returned. table_name is the name of the table from which the data is to be retrieved. The WHERE clause is used to specify the condition that must be met for the rows to be returned.
Example:
SELECT *
FROM customers
WHERE ROWNUM <= 10
ORDER BY last_name;
In this example, the query returns the first 10 rows of the customers table sorted by last name.
These clauses can be very useful when dealing with large tables or when you only need to display a subset of data to the user.