When we work with a huge amount of data there are cases when we need to restrict the number of rows that should be returned as part of the query.

In order to help us with the same, SQL provides us with a feature called SQL Limit. This clause has to be the last one in the query otherwise you will get an error.

SQL Limit Clause

MySQL, PostgreSQL and many more databases support the usage of SQL Limit clause. The SQL query is executed and finally, the number of results specified by the LIMIT clause is returned. If the result set size is smaller than the rows specified by the LIMIT clause, then all the result set is returned.

Let’s try to understand the usage of SQL Limit clause.

SQL Limit Example


We will consider the following table for understanding SQL Limit in a better way.


StudentId StudentName State Country
1 Henry Wales UK
2 Rohit Delhi India
3 Steve London UK

Below is the query to create the table and insert some sample data. Note that I am using PostgreSQL, so you might have to make some changes to run it in other databases.

Now we will try to limit the display result to just 2 rows.


StudentId StudentName State Country
1 Henry Wales UK
2 Rohit Delhi India

SQL Limit

There are times when we need to limit the number of rows based on some condition. We will try to understand the scenario better in the next section.

SQL Limit with Where Clause

SQL Limit can also be used along with WHERE clause.

SQL Limit with Where Clause Syntax:

We will consider the same student table for this case as well.

Let us try to get one entry from the table with the country as UK.


StudentId StudentName State Country
1 Henry Wales UK

SQL Limit with Where clause

MySQL Limit

MySQL database also supports LIMIT clause. Above examples are from PostgreSQL database. Let’s look at some examples from MySQL database.

Here is the script to create a sample table with some data.

Let’s run a simple query to select only 3 rows from the Customer table.


We can use Order By clause with LIMIT too. In this case, the query will be executed and finally, only the number of rows specified by the limit clause will be returned.

Finally, let’s look at an example of using the LIMIT clause with WHERE condition.


MySQL Limit Offset

Most of the times, the limit clause is used to process bulk records with pagination. So it’s useful when it’s used with offset. The syntax of the limit clause with offset is:

The offset number specifies the rows to skip before the specified number of rows to be returned in the result. Let’s understand this with a simple example.


Our Customer table has 4 rows. So the first row is skipped and the next two rows are returned in the result. Let’s look at one more example for SQL limit offset query.

Here only the third row from the Customer table will be returned.


SQL LIMIT clause helps us in achieving pagination in our application. It’s very helpful if we have to process huge result-set data by limiting the result set size.

By admin

Leave a Reply

%d bloggers like this: