Limiting Results with LIMIT and TOP

When working with large datasets, you often don’t want to retrieve all the rows at once—especially when testing, paginating results, or displaying only a few top records. That’s where LIMIT and TOP come into play.

🎯 What You’ll Learn

• How to use the LIMIT clause in MySQL and PostgreSQL
• How to use the TOP keyword in SQL Server
• How to combine with ORDER BY for best results

🧮 Syntax: LIMIT (MySQL, PostgreSQL)

SELECT column1, column2 FROM table_name
LIMIT number;  

Example: Get only the first 3 rows

SELECT * FROM students
LIMIT 3 ;  

🧮 Syntax: TOP (SQL Server, MS Access)

SELECT TOP number column1, column2 FROM table_name;  

Example:

SELECT TOP 3 * FROM students;  

🧪 Let's Use Our Sample students Table

id	name	age	course	marks
1	Rahul Sharma	21	SQL	85.50
2	Anjali Mehta	22	SQL	91.75
3	Vikas Gupta	23	SQL	88.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	SQL	76.00

🔹 Showing Top Scorers

To get the top 2 students with highest marks:

MySQL / PostgreSQL

SELECT * FROM students ORDER BY marks DESC LIMIT 2 ;  

SQL Server

SELECT TOP 2 * FROM students ORDER BY marks DESC ;  

🔸 Pagination with OFFSET and LIMIT

You can also skip rows using OFFSET (MySQL/PostgreSQL):

SELECT * FROM students ORDER BY id
LIMIT 2 OFFSET 2 ;  

This skips the first 2 records and returns the next 2.

⚠️ Things to Note

Tip	Description
Use ORDER BY before LIMIT	Ensures consistent results
LIMIT is not standard SQL	Only supported in MySQL/PostgreSQL
TOP works only in SQL Server & MS Access	It’s placed right after SELECT
Use OFFSET for pagination	Like showing records 11–20 in a list

🧾 Real-World Examples

1. Show youngest 3 students:

SELECT * FROM students ORDER BY age ASC LIMIT 3 ;  

1. Show top 1 scorer in SQL course:

SELECT * FROM students WHERE course = 'SQL' ORDER BY marks DESC LIMIT 1 ;  

1. In SQL Server, show top 5 students by marks:

SELECT TOP 5 * FROM students ORDER BY marks DESC ;  

📌 Summary

• Use LIMIT (MySQL/PostgreSQL) or TOP (SQL Server) to control the number of results.
• Always use ORDER BY with them to get meaningful top/bottom data.
• Use OFFSET with LIMIT to paginate results efficiently.