Summary: In this tutorial, you will learn how to use MySQL LIMIT clause to constrain number of returned records in SQL SELECT statement.
Most of the times, when you work with master data tables which contain thousand to millions of records and you don’t want to write a query to get all the data from those tables because of application’s performance and high traffic between database server and application server. MySQL supports a cool feature called LIMIT to allow you to constrain the returned records with SELECT statement. Let take a look at the MySQL LIMIT syntax:
Let’s say you have a database table with 10000 records and you want to get just first N records, you can use the following query:
The MySQL LIMIT also allows you to get a range of records where you decide starting record number and how many records you want to retrieve. Here is the syntax of MySQL LIMIT to select a range of records:
In the query above, S is the starting record index. MySQL specifies that the first record starts with 0. N is the number of records you want to select.
Let’s practice MySQL LIMIT with several examples to have a better understanding.
If you want to get the first five employees in the table employees, you can use the following query:
1 |
SELECT firstname,lastname |
+-----------+-----------+
| firstname | lastname |
+-----------+-----------+
| Diane | Murphy |
| Mary | Patterson |
| Jeff | Firrelli |
| William | Patterson |
| Gerard | Bondur |
+-----------+-----------+
5 rows in set (0.00 sec)
Now if you want to get five employees from employee number 10 you can use MySQL LIMIT with offset as follows:
1 |
SELECT firstname,lastname |
+-----------+-----------+
| firstname | lastname |
+-----------+-----------+
| Foon Yue | Tseng |
| George | Vanauf |
| Loui | Bondur |
| Gerard | Hernandez |
| Pamela | Castillo |
+-----------+-----------+
5 rows in set (0.00 sec)
Related posts:
- Using MySQL SELECT Statement to Query Data
- How to Use MySQL Distinct to Eliminate Duplicate Rows
- Working with Database Table – Part I
- Managing Databases in MySQL
- Managing Database Index in MySQL
