🚀 SQL LIMIT Clause – Restrict Rows in Your Query Like a Pro

Want to fetch just the top 10 results or preview a few rows from a huge table?
The SQL LIMIT clause helps you control how many rows your query returns—making your results faster, cleaner, and easier to handle.

---

🧾 What is SQL LIMIT?

The LIMIT clause in SQL is used to restrict the number of rows returned by a SELECT query. It’s particularly useful when working with large datasets, or when building pagination features in applications.

📌 Think of LIMIT as a way to say: “Give me just the first N rows.”

---

📚 Syntax of LIMIT

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

• number = the maximum number of rows you want returned.

---

✅ Example 1: Get First 5 Employees

SELECT * 
FROM employees
LIMIT 5;

This will return the first 5 rows from the employees table.

---

🔁 Example 2: Combine ORDER BY with LIMIT

To get the top 3 highest paid employees, sort first and then limit:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

💡 Always use ORDER BY with LIMIT if you want the top or bottom N values.

---

🎯 LIMIT with OFFSET (Skip Rows)

Many databases support an OFFSET to skip rows before starting to return the result.

✅ Syntax:

SELECT column1, column2
FROM table_name
LIMIT number OFFSET skip;

🧾 Example: Skip first 5, then return next 5 rows

SELECT * 
FROM products
LIMIT 5 OFFSET 5;

This returns rows 6 to 10.

---

🔁 Pagination Use Case

For building paginated results:

• Page 1: LIMIT 10 OFFSET 0
• Page 2: LIMIT 10 OFFSET 10
• Page 3: LIMIT 10 OFFSET 20

📌 Most APIs and websites use this logic behind the scenes!

---

⚙️ Database Support for LIMIT

Database	Support	Notes
MySQL	✅ Full Support	Native support for LIMIT + OFFSET
PostgreSQL	✅ Full Support	Also supports FETCH FIRST
SQLite	✅ Full Support	Same syntax
SQL Server	❌ Uses TOP	Use SELECT TOP N instead
Oracle	❌ Uses ROWNUM or FETCH	Similar functionality but different syntax

---

🧪 Challenge: Practice It

Question: Return the names of the top 2 most expensive products in the products table.

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 2;

---

📝 Final Thoughts

The LIMIT clause is a simple yet powerful SQL tool that helps:

• Improve performance
• Control output size
• Power features like pagination or previewing data

✅ Use it with ORDER BY for meaningful sorting
✅ Use OFFSET for paging large datasets