How can I retrieve records X to Y using DBI?

[BBQ]

How does one go about selecting X number of rows on a query (e.g. with MySQL)? The desired effect would be to return results from 0-20, then 20-40, and so on.

Originally posted as a Categorized Question.

[btrott]

I'll expand on what chromatic wrote, because the LIMIT keyword can do even more than just that. :) In addition to specifying the max number of records to return, you can specify an offset of where to start in the records being returned. The syntax is

[LIMIT [offset,] rows]

So, for example, to view records 10-35, do this:

select log_id
from log_id
limit 10,25

Pretty neat! Consult the MySQL SELECT docs for more details and tips.

[chromatic]

You could use the SQL command LIMIT: SELECT * from presidents WHERE terms > 2 LIMIT 10; You can also use the DBI method fetchall_arrayref(), which will return a reference to a list of rows. Use an array slice to get at the ones you want:

# prepare and execute statement
my $array_ref = $sth->fetchall_arrayref();

# we want 0 through 20
foreach my $row (@$array_ref[0 .. 20]) {
    # do something
}