LIMIT / TOP Clause
Controlling Output Rows in SQL

Introduction

Let’s say your principal wants a report of just the top 3 students from Class 10A, not the entire list. That’s where LIMIT (in MySQL/PostgreSQL) or TOP (in SQL Server) becomes handy. These clauses help you retrieve only a portion of the results — the top few rows, not all of them.

1. Syntax – LIMIT (MySQL / PostgreSQL / SQLite)

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;

2. Syntax – TOP (SQL Server)

SELECT TOP number column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];

Sample Table – student_marks

CREATE TABLE student_marks (
  roll_no INT,
  name VARCHAR(50),
  class VARCHAR(10),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO student_marks VALUES
(1, 'Aarav Sharma', '10A', 'Maths', 85),
(2, 'Diya Iyer', '9B', 'Maths', 90),
(3, 'Rohit Menon', '10A', 'Maths', 78),
(4, 'Sneha Patil', '8C', 'Maths', 88),
(5, 'Mehul Agarwal', '9B', 'Maths', 92);

3. Example – LIMIT

Let’s list the top 3 students in Maths based on marks:

SELECT name, marks FROM student_marks
WHERE subject = 'Maths'
ORDER BY marks DESC
LIMIT 3;

name            | marks
----------------+-------
Mehul Agarwal  | 92
Diya Iyer      | 90
Sneha Patil    | 88

4. Example – TOP (for SQL Server)

SELECT TOP 3 name, marks FROM student_marks
WHERE subject = 'Maths'
ORDER BY marks DESC;

5. LIMIT with OFFSET (Pagination)

Suppose you want to skip the top 2 students and fetch the next 2. Use OFFSET:

SELECT name, marks FROM student_marks
WHERE subject = 'Maths'
ORDER BY marks DESC
LIMIT 2 OFFSET 2;

name          | marks
---------------+-------
Sneha Patil   | 88
Aarav Sharma  | 85

6. Real-World Use Case – Topper per Class

Let’s get the highest marks in Maths per class. You’ll need GROUP BY and a subquery for this:

SELECT class, MAX(marks) AS top_score
FROM student_marks
WHERE subject = 'Maths'
GROUP BY class
ORDER BY top_score DESC
LIMIT 3;

Best Practices

• Always use ORDER BY with LIMIT or TOP to ensure predictable results.
• Use OFFSET for pagination in apps or dashboards.
• Test in your specific SQL dialect — some use LIMIT, others use TOP.

Summary

The LIMIT and TOP clauses help you stay focused — extracting just the most relevant rows. Whether it's ranking students or paginating long result sets, these clauses give you precision and performance.

What’s Next?

With LIMIT and TOP mastered, you're ready to dive into subqueries — the art of nesting SQL queries for powerful data exploration.