SELECT prod_name FROM products LIMIT 5;

The above statement uses the `SELECT`

statement to retrieve a single column. `LIMIT 5`

instructs MySQL to return no more than five rows. The output from this statement is shown in the following:

+-----------+ | prod_name | +-----------+ | product 1 | | product 2 | | product 3 | | product 4 | | product 5 | +-----------+

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

SELECT prod_name FROM products LIMIT 5,5;

`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:

+------------+ | prod_name | +------------+ | product 6 | | product 7 | | product 8 | | product 9 | | product 10 | +------------+

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.

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.

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.

## MySQL 5 `LIMIT`

and `OFFSET`

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`

.