SQL SELECT LIMIT, TOP, and FETCH FIRST

---

When querying large databases, it’s often useful to limit the number of rows returned, especially during testing, debugging, or when you're interested in just a sample of the data. SQL provides several ways to restrict the number of rows returned in a query result: LIMIT, TOP, and FETCH FIRST. These clauses allow you to control the number of rows retrieved, but their syntax and support vary between different database management systems (DBMS).

In this blog post, we will explain how to use each of these clauses and explore their differences. We’ll also provide examples for using LIMIT, TOP, and FETCH FIRST in your SQL queries.

---

1. SQL SELECT LIMIT

The LIMIT clause is used in SQL to specify the maximum number of rows to return from the result set. It is commonly used in databases like MySQL, PostgreSQL, and SQLite.

Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

• number_of_rows: Specifies the number of rows to return.

Example:

Suppose we have a students table and we want to retrieve only the first 5 records:

SELECT first_name, last_name
FROM students
LIMIT 5;

Result:

first_name	last_name
John	Doe
Jane	Smith
Alice	Johnson
Bob	Brown
Mark	Lee

This query will return only the first 5 rows of the students table.

Important Notes:

• LIMIT is typically used in MySQL, PostgreSQL, and SQLite.
• You can also use LIMIT with an OFFSET to skip a specific number of rows and retrieve a subset. For example:

SELECT first_name, last_name
FROM students
LIMIT 5 OFFSET 5;

This query will skip the first 5 rows and return the next 5 rows.

---

2. SQL SELECT TOP

The TOP clause is used in SQL Server and Microsoft Access to limit the number of rows returned. Similar to LIMIT, TOP is useful when you need to retrieve a specific number of rows from a table.

Syntax:

SELECT TOP number_of_rows column1, column2, ...
FROM table_name;

• number_of_rows: Specifies the number of rows to return from the result set.

Example:

If you're working with SQL Server and want to retrieve the top 3 students from the students table:

SELECT TOP 3 first_name, last_name
FROM students;

Result:

first_name	last_name
John	Doe
Jane	Smith
Alice	Johnson

This query will return only the first 3 rows from the students table.

Important Notes:

• TOP is used in SQL Server and Microsoft Access.
• TOP can also be used with a percentage to return a specific percentage of rows. For example, to get the top 10% of rows:

SELECT TOP 10 PERCENT first_name, last_name
FROM students;

This query will return 10% of the rows from the students table.

---

3. SQL SELECT FETCH FIRST

The FETCH FIRST clause is part of the SQL:2008 standard and is supported in databases like DB2, Oracle, and PostgreSQL. It is used to limit the number of rows returned, similar to LIMIT and TOP, but with a slightly different syntax.

Syntax:

SELECT column1, column2, ...
FROM table_name
FETCH FIRST number_of_rows ROWS ONLY;

• number_of_rows: Specifies the number of rows to return.
• ROWS ONLY: This part is required to specify the number of rows to be fetched.

Example:

To retrieve the first 3 rows from the students table using FETCH FIRST, you would write:

SELECT first_name, last_name
FROM students
FETCH FIRST 3 ROWS ONLY;

This query will return the first 3 rows ordered by the last_name column.

---

Comparison of LIMIT, TOP, and FETCH FIRST

Here’s a quick comparison of the three clauses:

Feature	LIMIT (MySQL, PostgreSQL, SQLite)	TOP (SQL Server, MS Access)	FETCH FIRST (DB2, Oracle, PostgreSQL)
Syntax	LIMIT number_of_rows	SELECT TOP number_of_rows	FETCH FIRST number_of_rows ROWS ONLY
Used in	MySQL, PostgreSQL, SQLite	SQL Server, MS Access	DB2, Oracle, PostgreSQL
Offset support	Yes (via OFFSET)	No direct offset support	Yes (via OFFSET in some DBMS)
Order support	Yes, can be combined with ORDER BY	Yes, can be combined with ORDER BY	Yes, can be combined with ORDER BY

---

---

---