SQL Limit – MySQL Limit

Filed Under: SQL

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

Syntax:


SELECT column_name(s)
FROM table_name
LIMIT number

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

Student

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.


CREATE TABLE `student` (
  `StudentId` INT NOT NULL,
  `StudentName` VARCHAR(45) NULL,
  `State` VARCHAR(45) NULL,
  `Country` VARCHAR(45) NULL,
  PRIMARY KEY (`StudentId`),
  UNIQUE INDEX `StudentId_UNIQUE` (`StudentId` ASC) VISIBLE);

Insert into Student(StudentId,StudentName,State,Country) VALUES (1, 'Henry','Wales','UK'), (2, 'Rohit','Delhi','India'), (3, 'Steve','London','UK');

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


SELECT * FROM student limit 2;

Output:

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

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:

SELECT column_name(s)
FROM table_name WHERE condition
LIMIT number

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.


SELECT * FROM student where country= "UK" limit 1;

Output:

StudentId StudentName State Country
1 Henry Wales UK
SQL Limit with Where clause

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.


CREATE TABLE `Customer` (
  `CustomerId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerName` varchar(20) DEFAULT NULL,
  `CutomerAge` int(11) DEFAULT NULL,
  `CustomerGender` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`CustomerId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `Customer` (`CustomerId`, `CustomerName`, `CutomerAge`, `CustomerGender`)
VALUES
	(1, 'John', 31, 'M'),
	(2, 'Amit', 25, 'M'),
	(3, 'Annie', 35, 'F'),
	(4, 'Tom', 38, 'M');

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


select * from Customer limit 3;

mysql limit

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.


select * from Customer order by CustomerName limit 2;

mysql limit with order by clause

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


select * from Customer where CustomerGender = 'M' limit 2;

MySQL 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:


SELECT column_name(s)
FROM table_name
LIMIT offset_number, number

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.


Select * from Customer limit 1, 2;

mysql limit offset

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.


select * from Customer limit 2, 1;

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

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages