Limiting Search Results with LIMIT

The final clause of the SELECT statement we will look at in this chapter is LIMIT .

The LIMIT clause is used to limit the number and range of rows that are returned from a query. For example, consider the following query:

 select * from employeeSkills limit 5; 

This query will return only the first five rows that match the selection criteria. In this particular case, we will simply get the first five rows found in the table, as shown here:

 +-------------+-------+  employeeID  skill  +-------------+-------+         6651  Java           6651  VB             7513  C              7513  Java           7513  Perl   +-------------+-------+ 5 rows in set (0.44 sec) 

We can also specify that we want a subset of rows other than the first n . If we, for example, wanted to retrieve rows 6 through 8 from the preceding query, we would do so like this:

 select * from employeeSkills limit 5, 3; 

When we pass two parameters to limit, the first parameter is the offset (start point) and the second parameter is the maximum number of rows we would like returned. Contrast this with the previous case: When we pass only a single parameter, it represents the maximum number of rows we would like returned.

Row numbering starts from zero when specifying offsets (as you can see in the preceding example ”for the sixth row, we specify offset 5). Our first LIMIT example selected rows 0 to 4, and our second selected rows 5 to 7.

If you specify the second parameter as -1 , the query will return the rows from the offset to the end of the table.

The LIMIT clause is normally used with ORDER BY so that the order in which rows are returned makes some sense. Remember that without an ORDER BY clause, the records are not retrieved in any logical order.

This clause is especially useful when building Web or GUI applications using MySQL because it provides an easy mechanism for paging results.