Tutorials›SQL›LIMIT and TOP
🟢 Free Demo
SQL TutorialTopic 6 of 20

LIMIT and TOP

Restrict how many rows are returned

✅ What You Will Learn

›How LIMIT restricts the number of rows returned
›The difference between LIMIT (MySQL/PostgreSQL) and TOP (SQL Server)
›How to use OFFSET for pagination
›Why you should always pair LIMIT with ORDER BY
›How to preview large tables safely

LIMIT (MySQL, PostgreSQL) and TOP (SQL Server) both restrict how many rows a query returns. This is essential when you want the "top 5 products", "latest 10 orders", or just want to preview data without pulling millions of rows.

In real analytics work, databases often contain millions of records. Running SELECT * without LIMIT can crash your query tool. Always use LIMIT when exploring data.

📋 The orders table used in examples

order_idcustomer_nameproductamountorder_date
1001Rahul SharmaLaptop450002026-01-15
1002Priya VermaMobile Phone180002026-01-16
1003Amit KumarHeadphones35002026-01-16
1004Sneha KapoorLaptop520002026-01-17
1005Vikram SinghTablet280002026-01-18

Syntax

SQL SYNTAX
-- MySQL / PostgreSQL
SELECT column1, column2
FROM table_name
LIMIT n;

-- SQL Server
SELECT TOP n column1, column2
FROM table_name;

Examples

Example 1Get top 3 highest orders (MySQL)
SELECT customer_name, product, amount
FROM orders
ORDER BY amount DESC
LIMIT 3;
OUTPUT
customer_name | product      | amount
--------------|--------------|-------
Sneha Kapoor  | Laptop       | 52000
Rahul Sharma  | Laptop       | 45000
Priya Verma   | Mobile Phone | 18000
💡

ORDER BY DESC first sorts highest to lowest, then LIMIT 3 takes only the first 3 rows.

Example 2Same query in SQL Server
SELECT TOP 3 customer_name, product, amount
FROM orders
ORDER BY amount DESC;
Example 3LIMIT with OFFSET — skip rows (pagination)
-- Show rows 6 to 10 (skip first 5)
SELECT customer_name, amount
FROM orders
ORDER BY amount DESC
LIMIT 5 OFFSET 5;
💡

OFFSET tells SQL how many rows to skip before starting. Used to build page-by-page results — page 1 gets rows 1–10, page 2 gets rows 11–20, and so on.

📌 Key Points to Remember

  • ✓LIMIT goes at the very end of the query
  • ✓Always use ORDER BY with LIMIT — otherwise the "top N" rows are random
  • ✓SQL Server uses TOP n before the column list, not at the end
  • ✓OFFSET lets you skip rows for pagination
  • ✓Use LIMIT 10 when first exploring a new table to preview its data safely

🏢 Real-World Application

LIMIT is used constantly in production analytics. When a data analyst first connects to a new database table with millions of rows, they always start with SELECT * FROM table_name LIMIT 10 to preview the structure without overloading the system. Dashboards showing "Top 10 Products This Week" use ORDER BY revenue DESC LIMIT 10. Paginated reports showing 25 rows per page use LIMIT 25 OFFSET 0 for page 1, LIMIT 25 OFFSET 25 for page 2, and so on. LIMIT is your safeguard on large datasets.

⚠️ Common Mistakes to Avoid

WRONGUsing LIMIT without ORDER BY
FIXWithout ORDER BY, LIMIT returns an arbitrary set of rows — not necessarily the highest, lowest, or most recent. Always sort first, then limit.
WRONGUsing LIMIT syntax in SQL Server
FIXSQL Server uses SELECT TOP N, not LIMIT. MySQL and PostgreSQL use LIMIT. Know which database you are on before writing the query.
WRONGExpecting LIMIT to make the query faster on large tables
FIXLIMIT reduces the output rows but does not always prevent a full table scan. Add appropriate WHERE filters and indexes to truly improve performance on large datasets.

❓ Frequently Asked Questions

What is LIMIT in SQL?

LIMIT restricts how many rows a query returns. SELECT * FROM orders LIMIT 5 returns only the first 5 rows. It is used with ORDER BY to get top-N results, and with OFFSET for pagination.

What is the difference between LIMIT and TOP in SQL?

They do the same thing but with different syntax. MySQL and PostgreSQL use LIMIT at the end: SELECT col FROM table LIMIT 10. SQL Server uses TOP at the start: SELECT TOP 10 col FROM table.

How does OFFSET work with LIMIT?

OFFSET skips a number of rows before starting. LIMIT 10 OFFSET 20 skips the first 20 rows and returns the next 10. This is the standard way to implement pagination in SQL.

How do I get the top 10 records in SQL?

Use ORDER BY to sort in the order you want, then LIMIT 10. For the highest values: SELECT * FROM orders ORDER BY amount DESC LIMIT 10. For most recent: SELECT * FROM orders ORDER BY order_date DESC LIMIT 10.

Does LIMIT improve SQL query performance?

LIMIT reduces the number of rows sent back to the client, which reduces network overhead. But the database may still scan the entire table to find and sort rows before applying LIMIT. Use WHERE and proper indexes for real performance gains.

✏️ Practice Exercise

Write a query to find the 5 most recent orders by order_date.

← PreviousORDER BYNext →Aggregate Functions
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.