Using SQL LIMIT in MySql

LIMIT is used to specify the number of rows we want to get in the result set using SELECT statement. Most of the times we don't want to get all the records from the table but a specific number of rows, in that case we can use LIMIT.

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)

MySQL LIMIT syntax and example -

Suppose from the above table we just want to get the top 4 results, we can use the following given statement -

SELECT * FROM CUSTOMER LIMIT 4;

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
+----+--------+---------+------+-----------+
4 rows in set (0.00 sec)

We can see that we have got just first 4 rows for the given query.

MySQL LIMIT with offset value syntax and example -

Suppose we don't want to get top some values but we want to get 3 values from second row, we can set offset 1 and then we will get values from 2nd row.

SELECT * FROM CUSTOMER LIMIT 1,3;

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
+----+--------+---------+------+-----------+
3 rows in set (0.00 sec)

Here we can see that we have got 3 values starting from 2nd row and we have left 1 row from the top.