How can you retrieve a particular number of records from a table in MySQL?
LIMIT clause is used with the SQL statement to retrieve a particular number of records from a table. From which record and how many records will be retrieved are defined by the LIMIT clause.
Example:
Get all records.
mysql> select * from shopping.customer;
+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo | Address | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
| 1 | Tarun | 9999075499 | Madan Puri | 124 | 122001 | 91 |
| 2 | Ram | 9650423377 | A-487 | 11 | 110085 | 91 |
| 3 | Sham | 1111111111 | A-485 | 11 | 110085 | 91 |
| 4 | Mohan | 1234567890 | 454 | 124 | 122002 | 91 |
+------------+--------------+------------+------------+--------+------------+-----------+
4 rows in set (0.00 sec)
Get top 2 records.
mysql> select * from shopping.customer LIMIT 2;
+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo | Address | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
| 1 | Tarun | 9999075499 | Madan Puri | 124 | 122001 | 91 |
| 2 | Ram | 9650423377 | A-487 | 11 | 110085 | 91 |
+------------+--------------+------------+------------+--------+------------+-----------+
2 rows in set (0.00 sec)
Get 2nd and 3rd record.
mysql> select * from shopping.customer LIMIT 1, 2;
+------------+--------------+------------+---------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo | Address | CityID | PostalCode | CountryID |
+------------+--------------+------------+---------+--------+------------+-----------+
| 2 | Ram | 9650423377 | A-487 | 11 | 110085 | 91 |
| 3 | Sham | 1111111111 | A-485 | 11 | 110085 | 91 |
+------------+--------------+------------+---------+--------+------------+-----------+
2 rows in set (0.00 sec)