SQL SELECT TOP Clause

The SELECT TOP clause in SQL only returns the specified number of rows from the table. It is valuable on enormous tables with a large number of records. Returning countless records can affect execution.

Note: Not all database systems support the SELECT TOP clause.

The SQL TOP keyword is utilized with these database systems:

• SQL Server
• MS Access 

Syntax

SELECT column1, column2, … TOP count
FROM table_name
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Here,

• column1, column2 = names of columns
• count = number of records to be fetched

SQL SELECT TOP Clause Example

Let’s understand this using an example of SQL SELECT TOP statement.

We will use the following table for this example:

Write the following SQL queries to create this table

Using SELECT TOP Clause in SQL

In this example, we will fetch the top 4 rows from the table.

Query:

SELECT TOP 4*
FROM Customer;

Output:

SQL SELECT TOP with ORDER BY Clause Example

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 Customer
ORDER BY Salary DESC;

Output:

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;

Output:

The above query will select all the employees according to the given condition (i.e. all Employees except the employee whose salary is less than 2000 will be selected) then the result will be sorted by Salary in ascending order (The ORDER BY keyword sorts the records in ascending order by default). Finally, the first 2 rows would be returned by the above query.

SQL SELECT TOP PERCENT Clause Example

The PERCENT keyword is utilized to select the primary and percent of all-out rows. For example,

Query:

SELECT TOP 50 PERCENT*
FROM Employee;

Output:

Here, the above query will select the first 50% of employee records out of the total number of records(i.e., the first 3 rows will be returned). 

SQL TOP PERCENT with WHERE Clause Example

We can also include some situations using the TOP PERCENT with the WHERE clause in the above query.

Query:

SELECT TOP 50 PERCENT*
FROM Employee
WHERE Salary<50000;

Output:

The above query will select the Top 50% of the records out of the total number of records from the table according to the given condition such that it returns only the Top 50% of the records with the employee whose salary is less than 5000 (i.e, 2 rows will be returned)

SQL TOP, LIMIT, and FETCH FIRST clauses are used to retrieve a specific number of records from a table. These clauses are especially useful in large datasets with thousands of records. Each of these SQL clauses performs a similar operation of limiting the results returned by a query, but they are supported by different database management systems:

• SQL TOP Clause is used in SQL Server and Sybase to limit the number of records returned.
• SQL LIMIT Clause is utilized in MySQL, PostgreSQL, and SQLite.
• SQL FETCH FIRST Clause is part of the SQL standard and is supported by Oracle, DB2, PostgreSQL, and SQL Server (as part of OFFSET-FETCH).

Depending on the database management system (DBMS) being used, you can utilize the respective clause to efficiently manage data retrieval. This article will provide examples and guidance on how to use the SQL TOP, LIMIT, and FETCH FIRST clauses in different SQL environments.