LIMIT and OFFSET in SQL

SQL Tutorials

LIMIT and OFFSET in SQL help you control how many records a query returns. When tables grow large, showing all rows at once becomes slow and confusing. Therefore, developers use LIMIT and OFFSET to display data in smaller and more useful chunks.

Because of this, these clauses play a key role in pagination, reports, and dashboards.

Sample Table Used for LIMIT and OFFSET

Before looking at queries, let us use this table for all examples.

students

id	name	age	city	marks
1	Rahul	20	Delhi	85
2	Neha	18	Mumbai	92
3	Amit	22	Delhi	78
4	Priya	19	Pune	88
5	Karan	21	Mumbai	90
6	Ankit	23	Delhi	81

LIMIT Clause in SQL

The LIMIT clause in SQL restricts the number of rows a query returns. As a result, you can fetch only the data you need.

Syntax

SELECT*FROM table_nameLIMIT number;

Example

SELECT*FROM studentsLIMIT3;

This query returns only the first three records from the table.

Using LIMIT with ORDER BY

Often, you want the top or bottom results instead of random rows. Therefore, LIMIT works best with ORDER BY.

SELECT*FROM studentsORDERBY marks DESCLIMIT3;

This query shows the top three students based on marks.

OFFSET Clause in SQL

The OFFSET clause in SQL skips a fixed number of rows before returning results. Because of this, OFFSET helps you move forward in a dataset.

Syntax

SELECT*FROM table_nameLIMIT number OFFSET skip;

Example

SELECT*FROM studentsLIMIT3 OFFSET 2;

This query skips the first two rows and then returns the next three rows.

LIMIT and OFFSET Together

When you use LIMIT and OFFSET together, you can build page-based results easily.

SELECT*FROM studentsORDERBY idLIMIT2 OFFSET 4;

Here, SQL skips four records and then returns the next two.

Real World Use of LIMIT and OFFSET

Developers use LIMIT and OFFSET in
blog listings
product pages
search results
admin dashboards

As a result, applications load faster and feel smoother.

Important Note

However, not all databases use the same syntax. For example, SQL Server uses OFFSET FETCH instead. Still, MySQL, PostgreSQL, and SQLite fully support LIMIT and OFFSET.