How to use Limit in MySQL Query - MySQL Developer Tutorial

How to use Limit in MySQL Query

Limit is used to restrict the number of rows you want to returned by select statement in MySQL. Tables can have thousands or millions of rows and sometime we need to only get some rows to take a look, we can use Limit clause to restrict the number of rows returned by our select query in MySQL.

Syntax :

Here is the syntax for Limit, there are two parameters you can pass to Limit Count and Offset.

Select column1,Column2,.... from YourTableName
Limit Offset, Count;

Let's create a sample table with some data and then we will use Limit to retrieve records.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');

Use Limit With Count only: 

Now let's say that we want to get only two rows from customer table, we can use below statement.

select * from customer Limit 2;

In this example, we are not sure about the order of rows which will be returned. Above query will return only two records. If you want to sort ( order by ) the records and then get 2 top out of them, you can use below statement. If you have auto_increment enabled, by ordering ascending or descending you can get Oldest or newest Records. Same way if you have createdatetime for your rows, you can get oldetest and newest records by using order by and Limiting the rows in Select.

select * from customer order by id Limit 3 ;

Using Offset  with Limit : 

Offset is used to specify the offset of the first row to be returned. Let's say if you want to start after 2 rows, then you will be using below statement.

Select * from customer  Limit 2 Offset 2;

You can also use order by to make sure the order in which you would like to get the rows.