SQL LIMIT / TOP

The LIMIT clause (used in MySQL, PostgreSQL, SQLite) orTOP keyword (used in SQL Server) is used to restrict the number of rows returned by a query. This is especially useful when working with large datasets or when you only need a sample of results.

📌 Syntax

-- Using LIMIT (MySQL, PostgreSQL, SQLite)
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column
LIMIT number_of_rows;

-- Using TOP (SQL Server)
SELECT TOP number_of_rows column1, column2
FROM table_name
WHERE condition
ORDER BY column;

📊 Example Table: users

id | name    | age | city
---+---------+-----+---------
1  | Alice   | 22  | New York
2  | Bob     | 30  | London
3  | Charlie | 18  | Sydney
4  | Diana   | 27  | Toronto
5  | Ethan   | 35  | Paris
6  | Frank   | 28  | London
7  | Grace   | 24  | Paris

🔍 Example 1: Get Top 3 Oldest Users

Retrieve the 3 users with the highest age:

SELECT * FROM users ORDER BY age DESC LIMIT 3;

🔍 Example 2: Using LIMIT with OFFSET

Skip the first 2 rows and retrieve the next 3:

SELECT * 
FROM users 
ORDER BY age ASC 
LIMIT 3 OFFSET 2;

🔍 Example 3: SQL Server TOP

In SQL Server, use TOP instead of LIMIT:

SELECT TOP 3 *
FROM users
ORDER BY age DESC;

⚡ Best Practices

• Always use ORDER BY with LIMIT or TOP to ensure predictable results.
• Use OFFSET to paginate results efficiently.
• LIMIT is very useful for testing queries on large tables without loading all rows.
• Combine with WHERE or JOIN to fetch only relevant data.

📝 Summary

The LIMIT and TOP clauses allow you to control the number of rows returned in SQL queries. They are essential for optimizing performance, handling large datasets, and implementing pagination in applications.

🚀 Next Steps

Next, we will explore the SQL Aggregate Functions such as COUNT, SUM, AVG, MIN, and MAX, which allow summarizing and analyzing data.