The SQL TOP, LIMIT, and FETCH FIRST clauses are used to limit the number of rows returned by a query, helping retrieve only the required records from a table.
- TOP: Limits rows in SQL Server and Sybase.
- LIMIT: Limits rows in MySQL, PostgreSQL, and SQLite.
- FETCH FIRST: Limits rows in Oracle, DB2, and PostgreSQL.
SQL SELECT TOP Clause
The SELECT TOP clause returns only a specified number of rows from a table, helping improve query performance on large datasets. The SQL TOP keyword is utilized with these database systems:
- SQL Server
- MS Access
Syntax
SELECT TOP count column1, column2, ...
FROM table_name
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];
- column1, column2: names of columns.
- count: number of records to be fetched.
- WHERE conditions: (Optional) Filters the data based on conditions.
- ORDER BY expression: (Optional) Sorts the result set in ascending or descending order.
Let’s understand this using an example of SQL SELECT TOP statement. We will use the following table for this example:
Example 1: Using SELECT TOP Clause in SQL
In this example, we will fetch the top 4 rows from the table.
Query:
SELECT TOP 4 *
FROM employee;
Output:
Example 2: SQL SELECT TOP with ORDER BY Clause
In this example, we will use the SQL SELECT TOP clause with ORDER BY clause to sort the data in the results set.
Query
SELECT TOP 4 *
FROM employee
ORDER BY salary DESC;
Output:
Example 3: SQL SELECT TOP Clause with WHERE Clause Example
In this example, we will use the SELECT TOP clause with WHERE clause to filter data on specific conditions.
Query:
SELECT TOP 2 *
FROM employee
WHERE salary>2000
ORDER BY salary;