Limiting Results

SELECT statements return all matched rows, possibly every row in the specified table. To return just the first row or rows, use the LIMIT clause. Here is an example:

• Input

SELECT prod_name
FROM products
LIMIT 5;

• Analysis

The previous statement uses the SELECT statement to retrieve a single column. LIMIT 5 instj34ructs MySQL to return no more than five rows. The output from this statement is shown in the following:

• Output

+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Oil can        |
| Fuses          |
+----------------+

To get the next five rows, specify both where to start and the number of rows to retrieve, like this:

• Input

SELECT prod_name
FROM products
LIMIT 5,5;

• Analysis

LIMIT 5,5 instructs MySQL to return five rows starting from row 5. The first number is where to start, and the second is the number of rows to retrieve. The output from this statement is shown in the following:

• Output

+----------------+
| prod_name      |
+----------------+
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
| Bird seed      |
| Carrots        |
+----------------+

So, LIMIT with one value specified always starts from the first row, and the specified number is the number of rows to return. LIMIT with two values specified can start from wherever that first value tells it to.