The SQL TOP, LIMIT, FETCH FIRST, and ROWNUM clauses are used to limit the number of rows returned by a query. These clauses are used to limit the results of a query to a specified number of rows, making it easier to manage and manipulate the returned data. The syntax of each clause may vary slightly depending on the database management system being used.
Sample Data Table
Consider the following sample data table named “employees”:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | | 3 | Bob Smith| 60000 | | 4 | Alice | 65000 | | 5 | Tom | 70000 | +----+----------+--------+
SQL TOP Clause
The TOP clause is used in Microsoft SQL Server and Sybase SQL Anywhere to limit the number of rows returned by a query. The syntax of the TOP clause is as follows:
SELECT TOP number column1, column2, ... FROM table_name;
Where “number” is the number of rows you want to return and “column1”, “column2”, etc. are the columns you want to return data for. The query will return the specified number of rows from the top of the result set, ordered by the order in which the data is stored in the table.
For example, to return the top two rows from the “employees” table, you would run the following query:
SELECT TOP 2 ID, Name, Salary FROM employees;
This would return the following result:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | +----+----------+--------+
SQL LIMIT Clause
The LIMIT clause is used in MySQL and MariaDB to limit the number of rows returned by a query. The syntax of the LIMIT clause is as follows:
SELECT column1, column2, ... FROM table_name LIMIT number;
Where “number” is the number of rows you want to return and “column1”, “column2”, etc. are the columns you want to return data for. The query will return the specified number of rows from the top of the result set, ordered by the order in which the data is stored in the table.
For example, to return the first two rows from the “employees” table, you would run the following query:
SELECT ID, Name, Salary FROM employees LIMIT 2;
This would return the following result:
+----+----------+--------+ | ID | Name| Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | +----+----------+--------+
SQL FETCH FIRST Clause
The FETCH FIRST clause is used in IBM DB2 and Oracle to limit the number of rows returned by a query. The syntax of the FETCH FIRST clause is as follows:
SELECT column1, column2, ... FROM table_name FETCH FIRST number ROWS ONLY;
Where “number” is the number of rows you want to return and “column1”, “column2”, etc. are the columns you want to return data for. The query will return the specified number of rows from the top of the result set, ordered by the order in which the data is stored in the table.
For example, to return the first two rows from the “employees” table, you would run the following query:
SELECT ID, Name, Salary FROM employees FETCH FIRST 2 ROWS ONLY;
This would return the following result:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | +----+----------+--------+
SQL ROWNUM Clause
The ROWNUM clause is used in Oracle to limit the number of rows returned by a query. The syntax of the ROWNUM clause is as follows:
SELECT column1, column2, ...
FROM (SELECT column1, column2, ..., ROWNUM AS row_number
FROM table_name
WHERE ROWNUM <= number) WHERE row_number >= 1;
Where “number” is the number of rows you want to return and “column1”, “column2”, etc. are the columns you want to return data for. The query will return the specified number of rows from the top of the result set, ordered by the order in which the data is stored in the table.
For example, to return the first two rows from the “employees” table, you would run the following query:
SELECT ID, Name, Salary
FROM (SELECT ID, Name, Salary, ROWNUM AS row_number
FROM employees
WHERE ROWNUM <= 2) WHERE row_number >= 1;
This would return the following result:
+----+----------+--------+ | ID | Name | Salary | +----+----------+--------+ | 1 | John Doe | 50000 | | 2 | Jane Doe | 55000 | +----+----------+--------+
It is important to note that the ROWNUM clause should be used carefully, as it can affect the performance of your query. If a large number of rows are returned, the ROWNUM clause can slow down the query significantly.
In conclusion, the SQL TOP, LIMIT, FETCH FIRST and ROWNUM clauses are used to limit the number of rows returned by a query. They vary in syntax based on the database system you are using, but the basic idea is to specify the number of rows you want to return and the columns you want to return data for. The result will be a limited set of rows from the top of the result set, ordered by the order in which the data is stored in the table. When using these clauses, it is important to be mindful of performance as large result sets can slow down the query.
