📌 LIMIT, TOP, and FETCH are SQL clauses used to restrict the number of rows returned by a query. They are especially useful when working with large datasets, displaying paginated results, retrieving top-performing records, or improving query performance by fetching only the required rows.

📖 Why Limit Query Results?

By default, the SELECT statement returns all matching rows from a table. In many real-world applications, you only need a subset of the data, such as the first 10 products, the top 5 students, or the latest 20 orders.

📊 Sample Table

Consider the following Students table:

1️⃣ LIMIT (MySQL, PostgreSQL, SQLite)

The LIMIT clause returns only the specified number of rows from the result set.

SELECT column_name
FROM table_name
LIMIT number_of_rows;

SELECT *
FROM Students
LIMIT 3;

This query returns only the first 3 rows from the result set.

LIMIT with ORDER BY

To retrieve the highest or lowest values, combine LIMIT with ORDER BY.

SELECT Name,
       Marks
FROM Students
ORDER BY Marks DESC
LIMIT 3;

LIMIT with OFFSET

OFFSET skips a specified number of rows before returning results. It is commonly used for pagination.

SELECT *
FROM Students
LIMIT 2 OFFSET 2;

This query skips the first 2 rows and returns the next 2 rows.

2️⃣ TOP (Microsoft SQL Server)

SQL Server uses the TOP keyword to limit the number of rows returned.

SELECT TOP number_of_rows column_name
FROM table_name;

SELECT TOP 3 *
FROM Students;

TOP with ORDER BY

SELECT TOP 3 Name,
       Marks
FROM Students
ORDER BY Marks DESC;

TOP with Percentage

SQL Server also allows returning a percentage of rows.

SELECT TOP 50 PERCENT *
FROM Students;

3️⃣ FETCH FIRST (Oracle 12c+ and SQL Standard)

Oracle (12c and later) and several SQL-compliant databases support the FETCH FIRST clause.

SELECT column_name
FROM table_name
FETCH FIRST number_of_rows ROWS ONLY;

SELECT *
FROM Students
FETCH FIRST 3 ROWS ONLY;

FETCH FIRST with ORDER BY

SELECT Name,
       Marks
FROM Students
ORDER BY Marks DESC
FETCH FIRST 3 ROWS ONLY;

📊 Comparison of LIMIT, TOP, and FETCH

📄 Pagination Example

Pagination divides large datasets into smaller pages, making them easier to display in web applications.

SELECT *
FROM Students
ORDER BY StudentID
LIMIT 5 OFFSET 5;

If each page displays 5 records, this query skips the first 5 rows and returns the next 5.

💼 Real-World Example

Suppose a university portal wants to display only the top five students based on their marks.

SELECT Name,
       Department,
       Marks
FROM Students
ORDER BY Marks DESC
LIMIT 5;

On SQL Server, the equivalent query would use TOP 5, while Oracle would use FETCH FIRST 5 ROWS ONLY.

⚠️ Best Practices

🚀 Key Points to Remember