SQL LIMIT Clause | Top-N Queries

SQL LIMIT

Videos Discussion

In real-world databases, tables often contain thousands or millions of records.

But most of the time, we do not need all the data.

Examples

Show only top 5 highest-paid employees

Display latest 10 orders

Fetch first 20 records for pagination

Fetching all rows:

Slows down performance

Wastes memory

Makes results difficult to read

To solve this problem, SQL provides the LIMIT clause.

What Is the LIMIT Clause?

The LIMIT clause is used to restrict the number of rows returned by a query.

It tells SQL how many records should be displayed in the result set.

LIMIT is especially useful when working with:

Large datasets

Reports

Pagination

Top-N queries

Syntax

SELECT column1, column2
FROM table_name
LIMIT number;

Explanation

LIMIT number - specifies the maximum number of rows to return

Example: employees Table

id	name	salary	department
1	Amit	60000	IT
2	Neha	75000	HR
3	Rahul	50000	IT
4	Priya	90000	Finance
5	Karan	45000	HR
6	Anjali	82000	IT

Example - 1: Fetch Only First 3 Records

SELECT *
FROM employees
LIMIT 3;

Returns only the first 3 rows from the table.

Note: Without ORDER BY, the result order is not guaranteed.

LIMIT with ORDER BY (Top-N Queries)

Why ORDER BY Is Important with LIMIT

LIMIT alone just restricts the number of rows.

To get meaningful Top-N results, we must decide which rows come first. This is done using ORDER BY.

Example - 2: Top 3 Highest Paid Employees

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Returns the top 3 employees with the highest salaries.

Example - 3: Top 5 Lowest Paid Employees

SELECT name, salary
FROM employees
ORDER BY salary ASC
LIMIT 5;

Returns employees with the lowest salaries.

LIMIT with OFFSET (Skipping Rows)

Why OFFSET Is Needed

Sometimes we want to:

Skip initial rows

Implement pagination (page-wise data)

This is where OFFSET is used.

Syntax

SELECT *
FROM table_name
LIMIT limit_value OFFSET offset_value;

Example: Skip First 3 Records and Fetch Next 2

SELECT *
FROM employees
LIMIT 2 OFFSET 3;

Skips first 3 rows and returns the next 2 rows.

Pagination Example (Real-World Use)

Page 1 - LIMIT 10 OFFSET 0

Page 2 - LIMIT 10 OFFSET 10

Page 3 - LIMIT 10 OFFSET 20

Important Points About LIMIT

LIMIT is executed after WHERE and ORDER BY

Without ORDER BY, LIMIT results may be unpredictable

LIMIT improves performance for large tables

LIMIT syntax may differ across databases

Database Specific Note

Database	Syntax
MySQL / PostgreSQL	LIMIT
SQL Server	TOP
Oracle	ROWNUM

Two Minute Drill

LIMIT restricts the number of rows returned
Used for Top-N queries
Works beset with ORDER BY
OFFSET skips rows
Useful for pagination and performance
Without ORDER BY, results are not guaranteed