login

Subscribe to MySQL Tutorial websiteRSS Feed
 » Basic MySQL Tutorial » How to Use MySQL Limit to Constrain Number of Returned Records

How to Use MySQL Limit to Constrain Number of Returned Records

Earn extra cash at home! Thousands of MySQL related projects are waiting for you to finish.

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:

SELECT * FROM table
LIMIT N

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:

SELECT columns
FROM table
LIMIT S, N

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:

SELECT firstname,lastname
FROM employees
LIMIT 5
+-----------+-----------+
| 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:

SELECT firstname,lastname
FROM employees
LIMIT 10,5
+-----------+-----------+
| firstname | lastname  |
+-----------+-----------+
| Foon Yue  | Tseng     |
| George    | Vanauf    |
| Loui      | Bondur    |
| Gerard    | Hernandez |
| Pamela    | Castillo  |
+-----------+-----------+
5 rows in set (0.00 sec)
Related Tutorials
Search