SQL TOP, LIMIT, FETCH FIRST Clause

To select the first 10 rows in SQL, we can use the LIMIT clause in MySQL, TOP in SQL Server, or FETCH FIRST in Oracle database. 

SQL stands for Structured Query Language, which is used to manage, manipulate, and retrieve data from relational databases. SQL provides us a way to select a specific number of rows using clauses like LIMIT, TOP, or FETCH FIRST along with the ORDER BY statement. In this blog, we will learn all these clauses in more detail.

Table of Contents:

• TOP, LIMIT, FETCH FIRST Clause in SQL
  • 1. TOP Clause in SQL
  • 2. LIMIT Clause in SQL
  • 3. FETCH FIRST Clause in SQL
• Practical Examples of LIMIT Clause in SQL
• Conclusion
• FAQs

TOP, LIMIT, FETCH FIRST Clause in SQL

In SQL, we can use various clauses to select the first 10 rows. Let’s understand these clauses in detail to know how they work in different databases.

1. TOP Clause in SQL

The  TOP clause is used in the SQL server to limit the number of rows returned. It only returns the specified number of rows from the table. We can also use this with an ORDER BY statement to sort the resulting data set in ascending or descending order.

Syntax:

SELECT TOP 10 * FROM table_name;

2. LIMIT Clause in SQL

The Limit clause returns the first n number of records from the table. This command is used in databases like MySQL, PostgreSQL, and SQLite.

Syntax:

SELECT * FROM table_name LIMIT 10;

3. FETCH FIRST Clause in SQL

The FETCH FIRST command is used in databases, including Oracle, DB2, PostgreSQL, and SQL Server (with the OFFSET-FETCH clause). This command returns the specified number of rows from the table.

Syntax:

SELECT * FROM table_name FETCH FIRST 10 ROWS ONLY;

Practical Examples of LIMIT Clause in SQL 

Let’s learn the concept in more detail with some examples. Suppose we have a table Employees, with some basic details like: EmployeeID, name, position, and salary.

Table Employees:

By considering the above table, we are going to learn how we can fetch 10 rows either starting from the first row or the last row.

Example 1: How to select the first 10 rows in SQL?

Query:

SELECT * FROM employees LIMIT 10;

Output:

Example 2: How to select the last 10 rows in SQL?

Query: 

SELECT * FROM employees ORDER BY EmployeeID DESC LIMIT 10;

Output:

Conclusion

So far in this article, we have learned how to select the first 10 rows of a table. We have also explored methods for retrieving a specific number of rows in different databases, such as Oracle, PostgreSQL, and SQL Server. If you want to learn more about SQL, you can explore our SQL Course.

FAQs