A simple way of doing this is using 'LIMIT/OFFSET' type of SQL statements. I don't think they are in the official SQL specs, but most SQL servers have some kind of way to just return a limited subset of what you want. MySQL for instance uses this syntax:
SELECT * FROM BLAH ORDER BY ID LIMIT 40 OFFSET 100
to limit your query to items 100-139 in the BLAH table. This makes it easy to write a pager for a table, see for instance Class::DBI::Plugin::Pager
, which is a pager for the Class::DBI
This approach doesn't take insertion of rows into account, which may or may not be a problem, depending on how and how often your table changes.