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.
Caution
Row 0 The first row retrieved is row 0, not row 1. As such, LIMIT 1,1 will retrieve the second row, not the first one.
Note
When There Aren't Enough Rows The number of rows to retrieve specified in LIMIT is the maximum number to retrieve. If there aren't enough rows (for example, you specified LIMIT 10,5, but there were only 13 rows), MySQL returns as many as it can.
Tip
MySQL 5 LIMIT Syntax Does LIMIT 3,4 mean 3 rows starting from row 4, or 4 rows starting from row 3? As you just learned, it means 4 rows starting from row 3, but it is a bit ambiguous.
For this reason, MySQL 5 supports an alternative syntax for LIMIT. LIMIT 4 OFFSET 3 means to get 4 rows starting from row 3, just like LIMIT 3,4.
